Hi all,
Currently I am new to vba and i will need to use both index match and concat in vba. Currently my method is just renaming the cell as followed
Range("B2").Value = "=INDEX('Data'!$D:$D,MATCH(CONCAT($A2,B$1),'Data'!$E:$E,0))"
Then i do record macro to pull it down to the last row and column. Is there a better way to do this? As I tried with index match but I do not know how to put concat into VBA
And for the 2nd question. Is it possible to do as followed. Have a command button in sheet 1 to run a command button in sheet 2. As with my current code, it is running the code in sheet 1 at 2 instead. I do not want that I just want the code to stay running in sheet 2. From what i search online it is due to the code in sheet 2 containing as active sheet. Is there an easier way to change it instead of getting rid of active sheet. The code below is what I am using where data is the name of sheet 2
ThisWorkbook.Worksheets("Data").CommandButton1.Value = True
Thank you very much
Currently I am new to vba and i will need to use both index match and concat in vba. Currently my method is just renaming the cell as followed
Range("B2").Value = "=INDEX('Data'!$D:$D,MATCH(CONCAT($A2,B$1),'Data'!$E:$E,0))"
Then i do record macro to pull it down to the last row and column. Is there a better way to do this? As I tried with index match but I do not know how to put concat into VBA
And for the 2nd question. Is it possible to do as followed. Have a command button in sheet 1 to run a command button in sheet 2. As with my current code, it is running the code in sheet 1 at 2 instead. I do not want that I just want the code to stay running in sheet 2. From what i search online it is due to the code in sheet 2 containing as active sheet. Is there an easier way to change it instead of getting rid of active sheet. The code below is what I am using where data is the name of sheet 2
ThisWorkbook.Worksheets("Data").CommandButton1.Value = True
Thank you very much