If then else statement

[racheltaylor]

Hi
I am relatively new to VBA with basic knowledge and need help writing a macro..

I have text in column V of my worksheet, the value of cells in column V is either "Clear", "Pending" or blank "".

I want to write a macro that will input the date the current date into columns U and W IF the value in V on the same row = "Clear" and columns W and V are blank, and ONLY if they are blank. I don't want to override previous dates.
I need the date to be static so I thought copying and pasting values from Today() would produce a static date...

If the values in column V = "pending" or "" then nothing is to happen to the adjacent cells in columns W and V

My problem is I'm not sure how to indicate a range in VBA i.e. column V, as the range is constantly growing.

Any help with this would be much appreciated!

U	V	W
Response Date	Result	Date Emailed
	Clear
	Clear
	Clear
	Clear
	Pending
	Clear

<tbody>
</tbody>

 

[exceldevs]

Hi,

Try this code:

Sub FillDates()
Dim rw As Integer


rw = 2
Do
If ActiveSheet.Cells(rw, 22) = "Clear" And ActiveSheet.Cells(rw, 21) = "" And ActiveSheet.Cells(rw, 23) = "" Then
ActiveSheet.Cells(rw, 21) = Now()
ActiveSheet.Cells(rw, 23) = Now()
Else
rw = rw + 1
End If
Loop Until rw >= 100 'Change the loop range that will suit your need.

End Sub

 

[racheltaylor]

Works like a dream! thank you so much

 

[ukmikeb]

Hi

Welcome to the forum.

You asked :-

Hi
My problem is I'm not sure how to indicate a range in VBA i.e. column V, as the range is constantly growing.

which the above solution requires changing your code when the range changes.

The following :-

Sub CompleteDates()
Dim LR As Long
Dim Cell As Range

LR = Range("V" & Rows.Count).End(xlUp).Row

Range("V2:V" & LR).AutoFilter
Range("V2:V" & LR).AutoFilter Field:=1, Criteria1:="=Clear", Visibledropdown:=False

For Each Cell In Range("V2:V" & LR).SpecialCells(xlCellTypeVisible)

If IsEmpty(Cell.Offset(, -1).Value) And IsEmpty(Cell.Offset(, 1).Value) Then
    Cell.Offset(, -1).Value = Format(Now(), "dd/mm/yyyy")
    Cell.Offset(, 1).Value = Format(Now(), "dd/mm/yyyy")
End If

Next Cell

Range("V2:V" & LR).AutoFilter
End Sub

gives you the flexibility of a dynamic range.

hth

 

[racheltaylor]

Thank you for that, the task is executed much more quickly using this code.

Thanks!

 

[racheltaylor]

Thank you for that, the task is executed much more quickly using this code.