Semper
05-16-2020, 06:17 AM
Hey
First of all I have no idea about VBA and Macros. Normal Excel formulas are not a problem, but VBA is a number too high for me.:( That's why I hope for your help.
I have the following problem:
A value is written using a formula in cell A1 (example: A1 =C1+D1)
a random number should then be generated in B1.
The number generated in B1 must not change unless A1 changes.
and all from A1:A1000
With the code it only works if you type in the number manually. But not if an external source or the value is output from a formula.
Private Sub Worksheet_Change(ByVal Target As Range)
Set rngIntersect = Application.Intersect(Range("A1:A1000"), Target)
If Not rngIntersect Is Nothing Then
For Each cell In rngIntersect
cell.Offset(0, 1).Value = IIf(cell.Value <> "", Int((55 - 25 + 1) * Rnd + 25), "")
Next
End If
End Sub
I am unable to rewrite the code to generate the random number when a formula returns a value.
I found this article and tried to rewrite the code myself but I only get error messages.
https://superuser.com/questions/1298289/excels-worksheet-change-event-not-firing-unless-cell-is-manually-changed
Hope someone can help me: pray2:
First of all I have no idea about VBA and Macros. Normal Excel formulas are not a problem, but VBA is a number too high for me.:( That's why I hope for your help.
I have the following problem:
A value is written using a formula in cell A1 (example: A1 =C1+D1)
a random number should then be generated in B1.
The number generated in B1 must not change unless A1 changes.
and all from A1:A1000
With the code it only works if you type in the number manually. But not if an external source or the value is output from a formula.
Private Sub Worksheet_Change(ByVal Target As Range)
Set rngIntersect = Application.Intersect(Range("A1:A1000"), Target)
If Not rngIntersect Is Nothing Then
For Each cell In rngIntersect
cell.Offset(0, 1).Value = IIf(cell.Value <> "", Int((55 - 25 + 1) * Rnd + 25), "")
Next
End If
End Sub
I am unable to rewrite the code to generate the random number when a formula returns a value.
I found this article and tried to rewrite the code myself but I only get error messages.
https://superuser.com/questions/1298289/excels-worksheet-change-event-not-firing-unless-cell-is-manually-changed
Hope someone can help me: pray2: