Alternsti
12-19-2021, 02:33 PM
Hi everyone,
I want to update several lines/columns into a worksheet (ex. WStarget) from another one (ex. WSmaster).
1st: For every lines into WBmaster (ex. L2 to L250) I must find where this "partial string" is located inside WStarget (ex. X2-X1000).
2nd: When I found a match inside WStarget, I want to paste specific columns from WSmaster inside specific columns inside WStarget.
I'm able to find where the "partial string" is located but I'm having problems when trying to fill several columns using Index Match. I wonder if it'll be simpler to use VBA instead.
(Inside WStarget)
=INDEX($A$2:$AA$284;EQUIV("*"&MSmaster!L2&"*";$AA$2:$AA$284;0);2)
(Inside WSmaster)
=INDEX('WStarget!$A$2:$AA$284;EQUIV("*"&L2&"*";'WStarget$AA$2:$AA$284;0);2)
Have yourself a Merry Christmas and Happy New Year btw!
VBA code I'm working on right now:
Const w1 As String = "WBtarget-WStarget (2021-12-17).xlsx"
Const w2 As String = "WMmaster.xlsx"
Sub LiveData_pdate()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim cl As Range, ws1Rng As Range, ws2Rng As Range, aCell As Range
Dim lRowW1 As Long, lRowW2 As Long
'~~> Define your workbook and worksheets here
Set wb1 = Workbooks(w1)
Set ws1 = wb1.Sheets(1)
Set wb2 = Workbooks(w2)
Set ws2 = wb2.Sheets(1)
'~~> Work with First workbook to get last row and define your range
With ws1
lRowW1 = .Range("E" & .Rows.Count).End(xlUp).Row
Set ws1Rng = .Range("E2:E" & lRowW1)
End With
'~~> Work with Second workbook to get last row and define your range
With ws2
.Range("A1", "D1").EntireColumn.Hidden = True
lRowW2 = .Range("E" & .Rows.Count).End(xlUp).Row
Set ws2Rng = .Range("E2:E" & lRowW2)
For Each cl In ws2Rng
'~~> Do the find
Set aCell = ws1Rng.Find(what:=cl.Value, LookIn:=xlValues)
'~~> Check if found or not. This is required else you will
'~~> get an error if no match found
If Not aCell Is Nothing Then
'
'~~> Do what ever you want here
'
End If
Next
End With
End Sub
I want to update several lines/columns into a worksheet (ex. WStarget) from another one (ex. WSmaster).
1st: For every lines into WBmaster (ex. L2 to L250) I must find where this "partial string" is located inside WStarget (ex. X2-X1000).
2nd: When I found a match inside WStarget, I want to paste specific columns from WSmaster inside specific columns inside WStarget.
I'm able to find where the "partial string" is located but I'm having problems when trying to fill several columns using Index Match. I wonder if it'll be simpler to use VBA instead.
(Inside WStarget)
=INDEX($A$2:$AA$284;EQUIV("*"&MSmaster!L2&"*";$AA$2:$AA$284;0);2)
(Inside WSmaster)
=INDEX('WStarget!$A$2:$AA$284;EQUIV("*"&L2&"*";'WStarget$AA$2:$AA$284;0);2)
Have yourself a Merry Christmas and Happy New Year btw!
VBA code I'm working on right now:
Const w1 As String = "WBtarget-WStarget (2021-12-17).xlsx"
Const w2 As String = "WMmaster.xlsx"
Sub LiveData_pdate()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim cl As Range, ws1Rng As Range, ws2Rng As Range, aCell As Range
Dim lRowW1 As Long, lRowW2 As Long
'~~> Define your workbook and worksheets here
Set wb1 = Workbooks(w1)
Set ws1 = wb1.Sheets(1)
Set wb2 = Workbooks(w2)
Set ws2 = wb2.Sheets(1)
'~~> Work with First workbook to get last row and define your range
With ws1
lRowW1 = .Range("E" & .Rows.Count).End(xlUp).Row
Set ws1Rng = .Range("E2:E" & lRowW1)
End With
'~~> Work with Second workbook to get last row and define your range
With ws2
.Range("A1", "D1").EntireColumn.Hidden = True
lRowW2 = .Range("E" & .Rows.Count).End(xlUp).Row
Set ws2Rng = .Range("E2:E" & lRowW2)
For Each cl In ws2Rng
'~~> Do the find
Set aCell = ws1Rng.Find(what:=cl.Value, LookIn:=xlValues)
'~~> Check if found or not. This is required else you will
'~~> get an error if no match found
If Not aCell Is Nothing Then
'
'~~> Do what ever you want here
'
End If
Next
End With
End Sub