Using a for loop to change dates based on user criteria

Alex89

New Member
Joined
May 30, 2019
Messages
34
Hi everyone,

I'm trying to create a for loop that iterates through rows of a data located on "Master Publisher Content." I am trying to code it so if data in column G is less than the user-specified start date, within cell "C2" in the "Enter Info" worksheet, then the cell will be changed to the dates in cell C2.

For example, if my start date in C2 is May 1st and there is data in column G, (G4 e.g) of the Publisher Content tab that is April 15th, I want to change the cell in G4 from April 15th to May 1st.

I've played around with the code quite a bit, there are some issues with an object not being defined within the for loop. Could someone explain the logic behind defining objects?

Thank you so much in advance!!! I appreciate it








Code:
Sub adjust_dates()




Dim Ctr As Long
Dim ctr1 As Long
Dim StartDate As Date
Dim wksheet As Worksheet




Set wksheet = ThisWorkbook.Worksheets("Enter Info")
Ctr = Range("G3", Range("G3").End(xlDown)).Count
StartDate = ThisWorkbook.Worksheets("Enter Info").Range("C2")


For i = 3 To Ctr
    
    If Cells(i, 7) < StartDate Then
        
        With wksheet
        ThisWorkbook.Worksheets("Enter Info").Range("C2").Select.Copy
        wksheet.Cells(i, 7).Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        End With
    End If
Next i






End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi everyone,

I was able to figure more of this out, but for whatever reason, the last 2 cells of both columns are not changing even though they should be.. Does anyone have thoughts on why?

Code:
Sub adjust_dates()




Dim Ctr As Long
Dim StartDate As Date
Dim EndDate As Date
Dim wksheet As Worksheet




Set wksheet = ThisWorkbook.Worksheets("Master Publisher Content")


StartDate = ThisWorkbook.Worksheets("Enter Info").Range("C2")
EndDate = ThisWorkbook.Worksheets("Enter Info").Range("E2")


With wksheet


Ctr = Sheets("Master Publisher Content").Range("H3", Range("H3").End(xlDown)).Count


For i = 3 To Ctr
    If Cells(i, 8) > EndDate Then
        Cells(i, 8).Value = EndDate
    End If
Next i


End With






With wksheet


Ctr = Sheets("Master Publisher Content").Range("G3", Range("G3").End(xlDown)).Count


For i = 3 To Ctr
    If Cells(i, 7) < StartDate Then
        Cells(i, 7).Value = StartDate
    End If
Next i


End With








End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top