[VBA] Merging concat and index match

Afpire93

New Member
Joined
Aug 7, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi and welcome to MrExcel.

i will need to use both index match and concat in vba

Run this macro on the sheet where you want the formula
VBA Code:
Sub IndexMatch()
  Dim lr As Long, lc As Long
  
  lr = Range("A" & Rows.Count).End(3).Row
  lc = Cells(1, Columns.Count).End(1).Column
  
  With Range("B2", Cells(lr, lc))
    .Formula = "=INDEX(Data!$D:$D,MATCH($A2&B$1,Data!$E:$E,0))"
    .Value = .Value
  End With
End Sub
 
Upvote 0
Thank you soo much. I have been trying it for a few days and did not manage to get it to work till now. Really appreciate it
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top