Basic VB help

[lutpoy28]

Hi

Could someone please advise of the command required to stamp the value found from a basic IF such as below once the IF statement has been met.

i.e once IF is met retain the value

Private Sub Worksheet_Calculate()

If Sheet1.Cells(2, 4) = Sheet1.Cells(4, 26) Then
Sheet1.Cells(5, 26) = Sheet1.Cells(5, 6)
Else
Sheet1.Cells(5, 26) = ""
End If


End Sub

[KevinTHFC]

I think you need to change it like this-

Private Sub Worksheet_Calculate()

If Sheet1.Cells(2, 4).value = Sheet1.Cells(4, 26).value Then
Sheet1.Cells(5, 26).value = Sheet1.Cells(5, 6).value
Else
Sheet1.Cells(5, 26).value = ""
End If


End Sub

[lutpoy28]

Thanks but still does not retain value, i know nothing about vb so mere novice. At present criteria is met and value flashes up in cell but then dissapears as cells(2,4) is not a constant and is ever changing by the second.

[lutpoy28]

I came up with this but i know its not right becuase if i have more than 1 in a row the screen just keeps repeating the macro and flashes, click escape and select end and result is correct but its not ending itself.

Private Sub Worksheet_Calculate()

If Sheet1.Cells(2, 4) = Sheet1.Cells(4, 26) Then
Sheet1.Cells(5, 6).Select
Selection.Copy
Sheet1.Cells(5, 26).Select
ActiveSheet.Paste
End If



If Sheet1.Cells(2, 4) = Sheet1.Cells(4, 27) Then
Sheet1.Cells(5, 6).Select
Selection.Copy
Sheet1.Cells(5, 27).Select
ActiveSheet.Paste
End If

End sub

[Ian]

You need to set up a marker so that the copy is only done once, something like this ...

Dim Flag As Boolean


Private Sub Worksheet_Calculate()

If Sheet1.Cells(2, 4).Value = Sheet1.Cells(4, 26).Value And Flag = False Then
Sheet1.Cells(5, 26).Value = Sheet1.Cells(5, 6).Value
Flag = True
Else
Sheet1.Cells(5, 26).Value = ""
End If


End Sub

[lutpoy28]

Thanks

If I ave multiple subs how do i tell it to RUN all subs rather than just the sub ive highlighted, do i need a command or something in between them.

Private Sub Worksheet_Calculate10()

If Sheet1.Cells(2, 4).Value = Sheet1.Cells(4, 26).Value And Flag = False Then
Sheet1.Cells(5, 26).Value = Sheet1.Cells(5, 6).Value
Flag = True
Else
Sheet1.Cells(5, 26).Value = ""
End If

End Sub



Private Sub Worksheet_Calculate9()

If Sheet1.Cells(2, 4).Value = Sheet1.Cells(4, 27).Value And Flag = False Then
Sheet1.Cells(5, 27).Value = Sheet1.Cells(5, 6).Value
Flag = True
Else
Sheet1.Cells(5, 27).Value = ""
End If

[Ian]

You don't need multiple - just put all the code in one worksheet_calculate.

Looks like you will need multiple flags though .. one for each IF statement.

You will also probably need to have some code that puts all the flags to false at some point eg. when you change market.

[lutpoy28]

Excuse my ignorance but as say never used VB before, what do you mean by multiple flags, if the below wrong then as only records the data for first IF

Private Sub Worksheet_Calculate()

If Sheet1.Cells(2, 4).Value = Sheet1.Cells(4, 26).Value And Flag = False Then
Sheet1.Cells(5, 26).Value = Sheet1.Cells(5, 6).Value
Flag = True
Else
Sheet1.Cells(5, 26).Value = ""
End If


If Sheet1.Cells(2, 4).Value = Sheet1.Cells(4, 27).Value And Flag = False Then
Sheet1.Cells(5, 27).Value = Sheet1.Cells(5, 6).Value
Flag = True
Else
Sheet1.Cells(5, 27).Value = ""
End If

End Sub

[lutpoy28]

similar to this ?

Havent got a clue how you clear a flag so will delete manually, lol



Private Sub Worksheet_Calculate()

If Sheet1.Cells(2, 4).Value = Sheet1.Cells(4, 26).Value And Flaga = False Then
Sheet1.Cells(5, 26).Value = Sheet1.Cells(5, 6).Value
Flaga = True
Else
Sheet1.Cells(5, 26).Value = ""
End If


If Sheet1.Cells(2, 4).Value = Sheet1.Cells(4, 27).Value And Flagb = False Then
Sheet1.Cells(5, 27).Value = Sheet1.Cells(5, 6).Value
Flagb = True
Else
Sheet1.Cells(5, 27).Value = ""
End If

If Sheet1.Cells(2, 4).Value = Sheet1.Cells(4, 29).Value And Flagc = False Then
Sheet1.Cells(5, 29).Value = Sheet1.Cells(5, 6).Value
Flagc = True
Else
Sheet1.Cells(5, 29).Value = ""
End If

If Sheet1.Cells(2, 4).Value = Sheet1.Cells(4, 31).Value And Flagd = False Then
Sheet1.Cells(5, 31).Value = Sheet1.Cells(5, 6).Value
Flagd = True
Else
Sheet1.Cells(5, 31).Value = ""
End If

End Sub

[Ian]

Yes something like that. Define each flag at the top eg

Dim flaga as Boolean
Dim flagb as Boolean etc. etc.