Erwin65456
New Member
- Joined
- Dec 19, 2020
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
I don't know what I'm doing wrong here.
on sheet 1, variable length data will be provided in column A. only the 1st 5 characters are needed from column A.
for the sake of the vlookup herein, there is data in columns 1 through 7 on Sheet 2.
Eventually in the end I will be filtering the list by Department and saving PDFs based on the Department, but somehow that is simple for me, yet these basics are difficult.
I have tried a bunch of different ways (you will see that the method I tried in the first paragraph is not consistently applied throughout the other paragraphs, as I could not get it right).
at this point it is embarrassing that I don't understand how copy and paste works in VBA (I have looked on various websites as well).
the main point here is how to get the below as hard numbers only, as I will be adding filtering code to it later when I can get this right.
thanks
on sheet 1, variable length data will be provided in column A. only the 1st 5 characters are needed from column A.
for the sake of the vlookup herein, there is data in columns 1 through 7 on Sheet 2.
Eventually in the end I will be filtering the list by Department and saving PDFs based on the Department, but somehow that is simple for me, yet these basics are difficult.
I have tried a bunch of different ways (you will see that the method I tried in the first paragraph is not consistently applied throughout the other paragraphs, as I could not get it right).
at this point it is embarrassing that I don't understand how copy and paste works in VBA (I have looked on various websites as well).
the main point here is how to get the below as hard numbers only, as I will be adding filtering code to it later when I can get this right.
thanks
VBA Code:
Sub Whatever()
'this creates column A data. first 5 characters only, and pastes the values with no formulas
Range("B1").FormulaR1C1 = "=left(C[-1],5)"
Range("B1").AutoFill Destination:=Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlUp)).Select
Selection.Copy
Range("C1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range(Selection, Selection.End(xlUp)).Select
Selection.Copy Range("B1")
Columns("A:B").Delete
'Range("B1").Paste 'column A (Code)
'this looks up a value from sheet 1 in column G based on sheet 2 column A values and pastes the values with no formula
Range("B1").FormulaR1C1 = "=VLOOKUP(C[-1],'Sheet1'!C[-1]:C[5],7,FALSE)"
Range("B1").AutoFill Destination:=Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
Range("B1").Selection.End(xlDown).Select
Selection.Copy
Range("C1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("B").Delete 'column B (Position Code)
'this takes a value from a different workbook in column B based on column A values and copies just the values with no formulas
Range("C1").FormulaR1C1 = _
"=VLOOKUP(LEFT(C[-1],9),'C:\Users\YourPath\DTH\[blahblah.xlsx]Sheet1'!C1:C3,2,FALSE)"
Range("C1").AutoFill Destination:=Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
Range("C1").Selection.End(xlDown).Select
Selection.Copy
Range("D1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("C").Delete 'column C (Title)
'same as the above except column C this time
Range("D1").FormulaR1C1 = _
"=VLOOKUP(LEFT(C[-2],9),'C:\Users\YourPath\DTH\[blahblah.xlsx]Sheet1'!C1:C3,3,FALSE)"
Range("D1").AutoFill Destination:=Range("D1:C" & Range("A" & Rows.Count).End(xlUp).Row)
Range("D1").Selection.End(xlDown).Select
Selection.Copy
Range("E1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("D").Delete 'column D (Department)
End Sub