Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Create a newworksheet with VBA code and put VBA code in the new worksheet module | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming |