enter formula in multiple cells based on another cell

  • Thread starter Thread starter Simon Clark
  • Start date Start date
S

Simon Clark

Hi all. Not quite sure how to do this, or the validity of Excel VBA!=

I have a column that contains different data but repeated. For
example every now and then, the value "simon" will apear for example
in e2, e10, e13. But this can change over time.

I want to be able to enter a formula(creating a result) within the
cell adjacent to it within column "G".

so..

if E2= "Simon" then
formula for cells adjacent

but taking this a step further i would like the formula to appear in
all the occurances of "Simon" within the same column.

Cheers...
Simon
 
You do not say how many names there are. If several this formula coul
get unwieldy, when some sort of lookup table might be better. Thi
hopefully shows the principle :-

=IF(condition, value if condition true, value if condition false)

Copied down column G (starting row G2) :-

=IF(E2="Simon",A1 +B1,IF(e2="Fred",C1 +D1,""
 
BrianB said:
You do not say how many names there are. If several this formula could
get unwieldy, when some sort of lookup table might be better. This
hopefully shows the principle :-

=IF(condition, value if condition true, value if condition false)

Copied down column G (starting row G2) :-

=IF(E2="Simon",A1 +B1,IF(e2="Fred",C1 +D1,"")
Click to expand...

cheers for that. Problem i have is what you pointed out.. the number
of times it can occur for.. well it can depend from one month to the
next.

I am therefore very very interested in learning how to make a loop in
VBA for this saving me copying the formula up or down.

Thanks..

SimonC
 
This should give you a start :-

'---------------------------------------------
Sub test()
Dim ToRow As Long
Dim Myname As String
Dim MyFormula As String
'--------------------------
ToRow = 2
'- loop until blank cell
While ActiveSheet.Cells(ToRow, 5).Value <> ""
'- upper case name
Myname = UCase(ActiveSheet.Cells(ToRow, 5).Value)
Select Case Myname
Case "SIMON"
MyFormula = "=A" & ToRow & "+B" & ToRow
Case "FRED"
MyFormula = "=A" & ToRow & "+C" & ToRow
Case Else
MyFormula = ""
End Select
ActiveSheet.Cells(ToRow, 7).Formula = MyFormula
ToRow = ToRow + 1
Wend
'-------------------------------------
End Sub
'-----------------------------------------
 
BrianB said:
This should give you a start :-

'---------------------------------------------
Sub test()
Dim ToRow As Long
Dim Myname As String
Dim MyFormula As String
'--------------------------
ToRow = 2
'- loop until blank cell
While ActiveSheet.Cells(ToRow, 5).Value <> ""
'- upper case name
Myname = UCase(ActiveSheet.Cells(ToRow, 5).Value)
Select Case Myname
Case "SIMON"
MyFormula = "=A" & ToRow & "+B" & ToRow
Case "FRED"
MyFormula = "=A" & ToRow & "+C" & ToRow
Case Else
MyFormula = ""
End Select
ActiveSheet.Cells(ToRow, 7).Formula = MyFormula
ToRow = ToRow + 1
Wend
'-------------------------------------
End Sub
'------------------------------------------
Click to expand...

Thanks for the reply. I have tested it and entered some values. It
works exactly how i want apart from when the loop has found a blank
cell after "Simon" or "Fred" it doesnt continue, so if i have any more
"Simon" or "Fred" after, it doesnt recognise. I am new to looping, so
is it because i need a NEXT function or something!?

Just for information i am using Excel 2002 (XP i think).

I would be grateful for your reply...

Simon
 
Thanks for the reply. I have tested it and entered some values. It
works exactly how i want apart from when the loop has found a blank
cell after "Simon" or "Fred" it doesnt continue, so if i have any more
"Simon" or "Fred" after, it doesnt recognise. I am new to looping, so
is it because i need a NEXT function or something!?

Just for information i am using Excel 2002 (XP i think).

I would be grateful for your reply...

Simon
Click to expand...

sorry to be a pain... also... how do you calculate the formula down
within the same column without specifying specifically the cells?

Cheers...

SimonC
 
Back
Top