What I am having trouble with is a twist to regular Vlookup vba code. Here, I have a table from G2:H9 in Sheet1. All of the cells listed in Column G2:G9 has tranposed values (Header) as the ones in the first row of Sheet3. When I run a macro, a value in H2, which corresponds to G2 will be copied and pasted into every cell in Column D in Sheet3 from row 2.
So, Basically, I have H2,H3,H4, ... , H9 in which the value in each of these cell will be copy/pasted into the entire column D, AC, X,Y, AE, AF, AD, F respectively from row2~ to the last row with data in sheet2(Imported data)
I will use the following code to determine the last row the value has to be pasted to. I believe this code will look for the last used row with data in sheet2.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim lastRow As Long
Dim rng As Range
Set rng = ThisWorkbook.Sheets(2).Cells
lastRow = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row</code>Or I might even consider using this to find out the last row
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets(2)
'Ctrl + Shift + End
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row</code>Is this even possible? I would very much appreciate any help on this. Thanks in advance
So, Basically, I have H2,H3,H4, ... , H9 in which the value in each of these cell will be copy/pasted into the entire column D, AC, X,Y, AE, AF, AD, F respectively from row2~ to the last row with data in sheet2(Imported data)
I will use the following code to determine the last row the value has to be pasted to. I believe this code will look for the last used row with data in sheet2.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim lastRow As Long
Dim rng As Range
Set rng = ThisWorkbook.Sheets(2).Cells
lastRow = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row</code>Or I might even consider using this to find out the last row
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets(2)
'Ctrl + Shift + End
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row</code>Is this even possible? I would very much appreciate any help on this. Thanks in advance
