I need to calculate a total. for instance, a1*b1 then take the answer and add it to what was in b1 to begin with. The only way i can think of is a two step process. Thanks
I need to calculate a total. for instance, a1*b1 then take the answer and add it to what was in b1 to begin with. The only way i can think of is a two step process. Thanks
Austen,
So you want B1 to be B1+(A1*B1) ? So if A1 contains 2 and B1 contains 4, you want B1 to then contain 12?
Unfortunately Paste Special doesn't allow you to multiply and add, so you would have to use a third column, then paste special the values back into B:
=B1+A1*B1
Matt
Thanks buddy. I was hping not to have to do it that way. BTW, on an unrelated subject what do you have to do to re-install te MSDN collection?
Paste special would have allowed you to Add something to B, or multiply A by B, but unfortunately for both operations you'd have to use the formula.
Not exactly sure about the MSDN thing, I would assume it would be on the VB CD. It isn't anything you can download that I'm aware of
ok thanks
[VBA]Sub DoCalculation()
[B1] = [B1+(A1*B1)]
End Sub[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Thanks John
It's easier with VBA (you avoid the circular reference problem). You could also make it a change event E.G.
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
[B1] = [B1+(A1*B1)]
Application.EnableEvents = True
End If
End Sub
[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Or, to apply it to the entire column, [VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
Target = Evaluate(Target + (Range("A" & Target.Row) * Target))
Application.EnableEvents = True
End If
End Sub[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
What exactly do you want installed with the MSDN Collection? Do you have the DVDs or web access to the site?
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Hi Zack. Im not really sure. My predicesor was a, well, I cant say that on here. Anyway he re engineered a bunch of Access code into SQL Server. Now the powers that be and me want something that is not so tied to SQL, stored procedures and some other junk. It is that reference (VB T-SQL) that the save function is looking for. Cant find it and as far as I know is not downloadable from MSN.
Posting Permissions
Reply With Quote
