Clear Cell Value before re-running the VBA

[captedgar]

Hi there

My Excel Vba shown below inserts time() into a particular cell on my sheet.
Once inserted, is it possible to clear this cell before my vba code is re-run?.
My VBA is only entering the time() once and not re-insering the time() if i change the same cell values after some time i.e.

please advice



Private Sub DateModifier(ByVal strTarget As String, ByVal strDestination As String)
' Checks for a Yes or NA or RB(Rollback) value in strTarget and if found, modifies the cell in strDestination
If (Range(strTarget + strCurrentRow).Value2 = "Yes") Then
If (Range(strDestination + strCurrentRow).Value2 = "") Then
Range(strDestination + strCurrentRow).Value2 = StaticTime
End If

' Check to see if Log has been rolled back
If (Range(strDestination + strCurrentRow).Value2 Like ("Rolled Back*")) Then
Range(strDestination + strCurrentRow).Value2 = StaticTime
End If
End If

' Check for an NA status
If (Range(strTarget + strCurrentRow).Value2 = "NA") Then
If (Range(strDestination + strCurrentRow).Value2 = "") Then
Range(strDestination + strCurrentRow).Value2 = "NA"
End If
End If

' Check to see if strTarget changes to "No" but originally was yes
' (if a value is in strDestination we assume a log has happened)
' and append the log with Rolled Back and the date
If (Range(strTarget + strCurrentRow).Value2 = "RB") Then
If Not (Range(strDestination + strCurrentRow).Value2 = "") Then
If Not (Range(strDestination + strCurrentRow).Value2 Like "*RB*") Then
Range(strDestination + strCurrentRow).Value2 = "RB" + StaticTime
End If
End If
End If
End Sub

[SamT]

First; We strongly suggest that you use Option Explicit in all your code.

Second; "+" is a math operator. It is best practice to always use "&" to concatenate Strings.

Third; It is best practice to never use Public Variables unless the procedure must modify them for use in another procedure. As written, we, and maybe VBA, can't know enough about strCurrentTime and StaticTime to properly parse your code.

Fourth; Without using Option Explicit, it is possible that strTarget is seen by VBA as an Cell Index and strCurrentRow as a Long so that adding (+) the two results in refering to a different Cell than you think.

Fifth; Value2 only applies to a Range Collection. Therefore when you Add (+) two variables that can be construed by VBA as Ranges and use a Range Collection Property, Value2, VBA may combine the two Variables as Ranges.

Sixth; Unless you have a good reason to assign a different time than the current time to the Cell represented by strDestination, substitute "Now" for "StaticTime."

Seventh; If you are working with Ranges, Use Range Vars, not String Vars.

Finally; This collage of lines in your code show the confusion generated by it.

' Checks for a Yes or NA or RB(Rollback) value in strTarget
' Check to see if strTarget changes to "No" but originally was yes
'
'If (Range(strDestination + strCurrentRow).Value2 Like ("Rolled Back*")) Then
'If Not (Range(strDestination + strCurrentRow).Value2 Like "*RB*") Then



You will need to attach the workbook for us to be able to fully understand your code.

[Bob Phillips]

Fifth; Value2 only applies to a Range Collection. Therefore when you Add (+) two variables that can be construed by VBA as Ranges and use a Range Collection Property, Value2, VBA may combine the two Variables as Ranges.

Where is he doing that?

[SamT]

(Range(strDestination + strCurrentRow).Value2

In "VBA may combine the two Variables " I should have said "might" instead of "may," 'cuz I don't know what will happen.

Who knows what evil lurks in the heartof VBA?

xld do.

[Bob Phillips]

BUt they are not range variables, they are strings, so although & is more obvious, I don't think there is any adding of ranges here. ANyways, you shouldn't & two ranges any more than + two ranges.