Search/Replace based on user input for one column

[willman32]

Hey All,

I am trying to make a macro that has a userform that asks the user for todays date. Then apply it to a column and replace all previous dates with the user input (today's date).

Pseudo-code-

if contents in column are less than user input:
replace with user input.


An example is:
A cell in column C has contents : 20110622
User input is 20110623
Replace the cell in column C with user input.

Thanks for looking at this thread

William

 

[VoG]

Try along the lines of

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long, x As Variant
LR = Range("C" & Rows.Count).End(xlUp).Row
x = Val(TextBox1.Value)
For i = 1 To LR
    If Range("C" & i).Value < x Then Range("X" & i).Value = x
Next i
End Sub

 

[willman32]

Hey VoG,

First, thanks for the reply to my thread, your help is greatly appreciated.

I've never used user forms, but I've created a userform and the only thing on it is an input box and a command button. How can I link the two together(code and userform) and run it on a macro, so that the box pops up then the code executes?

Again, thanks for your help.

Regards,

William

 

[VoG]

OK, right click the button on the userform, select View Code, delete whatever is there and paste in the code that I posted.

Insert > Module then paste in

Sub Test()
UserForm1.Show
End Sub

Press ALT + Q to close the code window, press ALT + F8, click on Test then click the Run button. Your userform should appear. Enter a date in the text box then click the command button.

 

[willman32]

Thank you SO much for your help!

You have saved me hours!!

 

[VoG]

I assume that it worked so thanks for the feedback.

 

[willman32]

Yep it has worked, perfectly I might add.

Just wanted to note, that I was looking for the replace of the data in situ, and the code was posting it in column X. I just simply changed it to C to tailor my needs. It is perfect as is, though, and thank you soooooooo much for your help and time.