Code Help
 

I am trying to develop code so that on the event that a1 is not blank the
cursor will go to B2 and take $A$1*B1. Then move over one cell to C2 and
take $A$1*C1 out to a100.

So far I have;


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$A$1" Then
Application.EnableEvents = False
If Target.Value < """" Then
'Need Help with Code"

End If
Application.EnableEvents = True
End If

End Sub

As always, thanks a lot for any help.

Code Help
 

Hi,
do you mean you want to apply a formula to B2:C100 ?

If so
Range("B2:C100").formula= "=$A$1*B1"
-- this will apply the formula string to B2 and update the string for all
other cells, ie in B2, the 'B1' portion of the formula becomes 'C1'

Now, if you only want values for the final (no formula), add the code:
Range("B2:C100").Copy
Range("B2:C100").pastespecial xlPasteValues
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Ronbo" wrote:

I am trying to develop code so that on the event that a1 is not blank the
cursor will go to B2 and take $A$1*B1. Then move over one cell to C2 and
take $A$1*C1 out to a100.

So far I have;


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$A$1" Then
Application.EnableEvents = False
If Target.Value < """" Then
'Need Help with Code"

End If
Application.EnableEvents = True
End If

End Sub

As always, thanks a lot for any help.

Code Help
 

I think you want to change

If Target.Value < """"

To

If Target.Value < ""

You are testing to see if the cell contains a quote " , not blank as you
stated.


"Ronbo" wrote:

I am trying to develop code so that on the event that a1 is not blank the
cursor will go to B2 and take $A$1*B1. Then move over one cell to C2 and
take $A$1*C1 out to a100.

So far I have;


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$A$1" Then
Application.EnableEvents = False
If Target.Value < """" Then
'Need Help with Code"

End If
Application.EnableEvents = True
End If

End Sub

As always, thanks a lot for any help.