Excel VBA Code for small scroll while there is a value on the right

Question

I have a Macro that takes data out of 2 reports. in the second report I have dates that I copy. I need to take a date and subtract from it 14 days

I go to first blank cell in column D, then I want to calculate the formula in column C and scroll down without type how many cells (because it is a macro to a daily basis and the amount of data will change). I want to do this until the end of the data I copied. In the end I want to copy it as values to column B.

Here is what I have in my code(part of all macro):

'first we go to the buttom of the column
'for NOW - change manually the top of the range you paste to
'Now, paste to OP_wb workbook:
OP_wb.Sheets("Optic Main").Range("D1").End(xlDown).Offset(1, 0).PasteSpecial 
Paste:=xlPasteValues
' Calculate Due Date to MFG tools
' it means date we copied from MFG daily minus 14 days
_wb.Sheets("Optic Main").Activate
Range("C1").End(xlDown).Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=RC[1]-14"enter code here

What is the problem? There is no question in your question. Also, which column in your screenshot is column D? Your description says that you have 3 columns with dates (B, C and D), but there is only one on your screenshot. — Egan Wolf, Jul 24 '17 at 08:39
My problem is that i dont know how to take the formula c=D-14 and to smallscroll down while there is a value in the right cell in column D. D - Description C - PN B - Due Date — Rafael Osipov, Jul 24 '17 at 09:00

score 0 · Answer 1 · answered Jul 24 '17 at 10:49

You need to loop from the first row to the last row. In general, there are plenty of good ways to define the last row of a given column. Once you have done it, replace the value of lngEndRow and run the following code:

Option Explicit

Public Sub TestMe()

    Dim lngStartRow As Long: lngStartRow = 1
    Dim lngEndRow   As Long: lngEndRow = 100
    Dim rngMyRange  As Range
    Dim rngMyCell   As Range

    With ActiveSheet
        Set rngMyRange = .Range(.Cells(lngStartRow, 5), .Cells(lngEndRow, 5))
    End With

    For Each rngMyCell In rngMyRange
        rngMyCell.FormulaR1C1 = "=RC[1]-14"
    Next rngMyCell

End Sub

Then change the ActiveSheet with the correct sheet and the column hardcoded as 5 with the correct one. Run the code above in an empty Excel, to understand what it does. Then change it a bit, until it matches your needs.

Thank you, Its great. But I have a different problem now. I want it to calculate not from row 1. I want it will start from end of data in row B. you have an idea how can I write it in lngStartRow? Range("C1").End(xlDown).Offset(1, -1).Activate — Rafael Osipov, Jul 24 '17 at 12:10
@RafaelOsipov - take a look at the link in my answer. Its probably the best source. And avoid `Activate` and `Select` in VBA - https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Vityata, Jul 24 '17 at 12:35

Excel VBA Code for small scroll while there is a value on the right

1 Answers1