Vba to find intersect cell and paste a value in it

[Juriemagic]

hi there, this is a code I have:

Sub COPY_TO_SHEET_2()
    MY_MATCH = Sheets("Sheet1").Range("C2").Value
    MY_OTHERMATCH = Sheets("Sheet1").Range("C3").Value   
    MY_VALUE = Sheets("Sheet1").Range("C4").Value
    With Sheets("Sheet2")
        For MY_ROWS = 1 To .Range("B" & Rows.Count).End(xlUp).Row
            If .Range("B" & MY_ROWS).Value = MY_MATCH Then
                For MY_COLS = 3 To Cells(1, Columns.Count).End(xlToLeft).Column
                    If Cells(1, MY_COLS).Value = MY_OTHERMATCH.Value Then
                        Cells(MY_ROWS, MY_COLS).Value = MY_VALUE
                        Exit Sub
                    End If
                Next MY_COLS
            End If
        Next MY_ROWS
    End With
End Sub

I received this code from Paul_Hossler on MrExcel. (a Great Thanx to him), however this code does not execute. I am hoping that someone would be so kind to please have a look at this code to see why it refuses to work. I am uploading a sample file. Thank you all very much for your time spent on this..oops, I get an error when I try to upload..

 

[NARAYANK991]

Hi ,

Can you rename your Excel workbook so that its extension is .txt or .docx or .zip , and then try to upload ?

In the same post mention whether the real extension is .xls or .xlsx or .xlsm or .xlsb

Narayan

 

[Juriemagic]

Hi ,

Can you rename your Excel workbook so that its extension is .txt or .docx or .zip , and then try to upload ?

In the same post mention whether the real extension is .xls or .xlsx or .xlsm or .xlsb

Narayan

it wouldn't seem that I can save as an extension which either supports multiple sheets, or is supported by the extensions the site is set up for..

 

[NARAYANK991]

Hi ,

You can save the file in what ever format you want ; from the Windows Explorer , rename the file by changing its extension to .txt , .docx , .zip and then try to upload that renamed file.

Narayan

 

[Juriemagic]

Hi ,

You can save the file in what ever format you want ; from the Windows Explorer , rename the file by changing its extension to .txt , .docx , .zip and then try to upload that renamed file.

Narayan

Finally got it right..(I learned something new today). The original extension is .xlsm..Thanx a lot..

 

[NARAYANK991]

Hi ,

I do not know what exactly you wish to do , but the problem with the posted code was only the following statement :

If Cells(1, MY_COLS).Value = MY_OTHERMATCH.Value Then

where the highlighted part is valid only if MY_OTHERMATCH is a range variable. If you remove it , and have it as :

If Cells(1, MY_COLS).Value = MY_OTHERMATCH Then

then the code will execute without any run time error.

Narayan

 

[Juriemagic]

Hi ,

I do not know what exactly you wish to do , but the problem with the posted code was only the following statement :

If Cells(1, MY_COLS).Value = MY_OTHERMATCH.Value Then

where the highlighted part is valid only if MY_OTHERMATCH is a range variable. If you remove it , and have it as :

If Cells(1, MY_COLS).Value = MY_OTHERMATCH Then

then the code will execute without any run time error.

Narayan

Hello,

I did as you said, but the code does not execute at all..it does not do anything. What I am trying to achieve is for the code to check the value in Sheet1 C2, and find the row in sheet2 with that number as row heading in column B, Then the code must check the value in Sheet 1, C3 and find that column with that number as heading..use this info to find the intersect cell and paste the value in Sheet1 C4, into this intersect cell...Please help..

 

[NARAYANK991]

Hi ,

That is a second problem ; the following lines had the Cells property unqualified. Adding a period . before the Cells keyword does this , so that it refers to the correct range.

For MY_COLS = 3 To Cells(1, Columns.Count).End(xlToLeft).Column
    If Cells(1, MY_COLS).Value = MY_OTHERMATCH Then
       Cells(MY_ROWS, MY_COLS).Value = MY_VALUE
       Exit Sub

See the file now.

Narayan

 

[Juriemagic]

M

Hi ,

That is a second problem ; the following lines had the Cells property unqualified. Adding a period . before the Cells keyword does this , so that it refers to the correct range.

See the file now.

Narayan

My goodness!!!..such a small thing that causes a total shutdown!!..I have learned again..From the deepest of my heart, I really appreciate your time spent on this...Thank you very very much!!