Hi All,
I am wondering if someone would be able to help. I have been looking online for a similar code for the below but I cannot find anything close. I am looking for a VBA that does the following:
I have data in sheet 4 column “I” if column “H” is blank I need to search in sheet 3 for the matching value in column “I”. If its not in sheet 3 it then needs to look in sheet 2 and again if not there sheet 1. Once it finds the matching value it should copy the matching row from column A:L. paste the value in sheet 4 on the row which triggered the search. After it has pasted the value wherever it copied from it needs to delete that row.
This will need to loop down column “I” in sheet 4 until there is no more to do. If values are already there as in I and H is completed then don’t edit it.
I was using this code that I found but it only searches in one column and my VBA knowledge doesn’t help me in how to change it to a row. But I’m not even sure this will work either. It runs but nothing shows on the excel file.
Sub CopyRng2()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim Rng As Range
Dim Col As Long
Dim i As Long
On Error Resume Next 'Without this macro crashes if there's no match
Set WS1 = Sheets("Sheet4")
Set WS2 = Sheets("Sheet1")
Set Rng = WS1.Range("A:I")
With WS2
'Finds the column to copy:
Col = Application.WorksheetFunction.Match(WS1.Range("I").Value, .Rows("1:1"), False)
'Write the values individually:
For i = 1 To 5
.Cells(Choose(i, 1, 30, 15, 2, 9), Col).Value = Rng.Cells(i).Value
Next i
End With
End Sub
Any help would be appreciated!
I am wondering if someone would be able to help. I have been looking online for a similar code for the below but I cannot find anything close. I am looking for a VBA that does the following:
I have data in sheet 4 column “I” if column “H” is blank I need to search in sheet 3 for the matching value in column “I”. If its not in sheet 3 it then needs to look in sheet 2 and again if not there sheet 1. Once it finds the matching value it should copy the matching row from column A:L. paste the value in sheet 4 on the row which triggered the search. After it has pasted the value wherever it copied from it needs to delete that row.
This will need to loop down column “I” in sheet 4 until there is no more to do. If values are already there as in I and H is completed then don’t edit it.
I was using this code that I found but it only searches in one column and my VBA knowledge doesn’t help me in how to change it to a row. But I’m not even sure this will work either. It runs but nothing shows on the excel file.
Sub CopyRng2()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim Rng As Range
Dim Col As Long
Dim i As Long
On Error Resume Next 'Without this macro crashes if there's no match
Set WS1 = Sheets("Sheet4")
Set WS2 = Sheets("Sheet1")
Set Rng = WS1.Range("A:I")
With WS2
'Finds the column to copy:
Col = Application.WorksheetFunction.Match(WS1.Range("I").Value, .Rows("1:1"), False)
'Write the values individually:
For i = 1 To 5
.Cells(Choose(i, 1, 30, 15, 2, 9), Col).Value = Rng.Cells(i).Value
Next i
End With
End Sub
Any help would be appreciated!
