Feb 03 2020 09:13 PM
I need a little help from the excel gurus out there. I have a spreadsheet where column c has data that changes in the number of cells filled down. What I need to do is find the last row with data in column c, move over 2 spaces to the left to A and enter a formula and then autofill that formula to A3. Next i need to find the last row with data in column C again and move over one space to the left to column B and enter a formula and then autofill it to B3.
So far this is what I have:
Range("C3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -2).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[2], R1C9)"
Selection.AutoFill Destination:=Range("A3:A251"), Type:=xlFillDefault
Range("c3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[1], LEN(RC[1])-R1C9-3)"
Selection.AutoFill Destination:=Range("B3:B251"), Type:=xlFillDefault
Range("B3:B251").Select
In the range cells i need A251 and B251 to be dynamic values equal to the number of rows with data in column c. Any ideas?
Feb 03 2020 10:00 PM
Feb 04 2020 08:22 AM
I tried this and it did not work. It did not copy the formula and autofill the cells going up. This is what I have:
Range("C3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -2).Select
Lrow = Cells(Rows.count, 3).End(xlUp).Row
Range("A3:A" & Lrow).FormulaR1C1 = "=LEFT(RC[2], R1C9)"
Feb 04 2020 09:01 AM - edited Feb 04 2020 09:02 AM
Try this!
Sub test()
Dim Lrow As Integer
Range("C3").Select
Selection.End(xlDown).Select
Lrow = Selection.Row
Range("A3").FormulaR1C1 = "helloA"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & Lrow), Type:=xlFillDefault
Range("B3").FormulaR1C1 = "helloB"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B22")
End Sub
Just replace the "helloA" and "helloB" with your formulae!
Feb 04 2020 10:33 AM - edited Feb 04 2020 10:43 AM
Sorry, I wasn't clear about what the code should replace (also, i've now been able to get on a machine and test it and replaced column numbers with letters, in my code - The following replaces the whole section you have shown in your original thread. Let me know if this works for you:
Dim Lrow As Long
Range("C3").Select
'Find last row number with value in column C
Lrow = Cells(Rows.Count, "C").End(xlUp).Row
'Select range A3 to A + Last row number previously found and enter formula
Range("A3:A" & Lrow).FormulaR1C1 = "=LEFT(RC[2], R1C9)"
Range("C3").Select
'Find last row number with value in column B
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
'Select range B3 to B + Last row number previously found and enter formula
Range("B3:B" & Lrow).FormulaR1C1 = "=RIGHT(RC[1], LEN(RC[1])-R1C9-3)"
Range("A1").Select
Feb 04 2020 10:45 AM