Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
No Subject
Afternoon group... you are all well I trust?
I have been writing VB stuff for years b ut only now has the requiremement fallen upon me to het involved with macros in excel and whereas the actual code of the macro is fine, I am having dificulties getting my head round the concepts. I have a specific problem that i would welcome comment or advice on: I have a worksheet that may or may not have a value entered ina specific cell. When a value is entered I need to grab that, do some work on it and update some cells on the same row. How on earth do I grab the reference for the cell that just changeds and how do i pick from that the row in order to address the cells further along. This sounds pretty basic but I have been scratching my head for two days now and can't seem to find a solution when googling - most likely I just can't get the wording right - I get loads of stuff but none of it really suits what i'm after.... HELP!!! all the best H |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Reply
Hi,
You can capture these values using the worksheet change event. Right click the sheet tab, view code and paste this in: Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Value & Chr(13) & Target.Address & Chr(13) & Target.Column & Chr(13) & Target.Row End Sub change a cell and a message pops up with the value, address, row and column Mike "feebo" wrote: Afternoon group... you are all well I trust? I have been writing VB stuff for years b ut only now has the requiremement fallen upon me to het involved with macros in excel and whereas the actual code of the macro is fine, I am having dificulties getting my head round the concepts. I have a specific problem that i would welcome comment or advice on: I have a worksheet that may or may not have a value entered ina specific cell. When a value is entered I need to grab that, do some work on it and update some cells on the same row. How on earth do I grab the reference for the cell that just changeds and how do i pick from that the row in order to address the cells further along. This sounds pretty basic but I have been scratching my head for two days now and can't seem to find a solution when googling - most likely I just can't get the wording right - I get loads of stuff but none of it really suits what i'm after.... HELP!!! all the best H |
#3
Posted to microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
One way:
You can get the cell reference from the Worksheet_Change() event's Target argument: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "A1" Then .Value = x ' "do some work on it" .Offset(0, 3).Value = y ' "update some cells on the same row" End If End With End Sub In article , feebo wrote: I have a specific problem that i would welcome comment or advice on: I have a worksheet that may or may not have a value entered ina specific cell. When a value is entered I need to grab that, do some work on it and update some cells on the same row. How on earth do I grab the reference for the cell that just changeds and how do i pick from that the row in order to address the cells further along. This sounds pretty basic but I have been scratching my head for two days now and can't seem to find a solution when googling - most likely I just can't get the wording right - I get loads of stuff but none of it really suits what i'm after.... HELP!!! |
#4
Posted to microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Thanks for replying. I got it covered in the end, used
Cells(Target.Row,x) like the following: If Target.Column = 4 And (Target.Row 1 And Target.Row < 51) Then a = Trim(Cells(Target.Row, Target.Column)) If a = "" Then For n = 7 To 12 Cells(Target.Row, n) = "" Next Else qty = AbsFromPack(a) ' grab the quantity required to absolute PackCal (Target.Row) ' calculate the pack details numpacks = qty / (Cells(Target.Row, 12)) If numpacks < Int(numpacks) Then numpacks = Int(numpacks) + 1 ' if we have a fraction of a pack, then bump it up to the next qty If Cells(Target.Row, 12) < "???" Then 'calculate the cost of the packs Cells(Target.Row, 7) = numpacks: Cells(Target.Row, 8) = (Cells(Target.Row, 5) / Cells(Target.Row, 12)) * qty End If End If End If On Tue, 18 Dec 2007 06:02:44 -0700, JE McGimpsey wrote: One way: You can get the cell reference from the Worksheet_Change() event's Target argument: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "A1" Then .Value = x ' "do some work on it" .Offset(0, 3).Value = y ' "update some cells on the same row" End If End With End Sub In article , feebo wrote: I have a specific problem that i would welcome comment or advice on: I have a worksheet that may or may not have a value entered ina specific cell. When a value is entered I need to grab that, do some work on it and update some cells on the same row. How on earth do I grab the reference for the cell that just changeds and how do i pick from that the row in order to address the cells further along. This sounds pretty basic but I have been scratching my head for two days now and can't seem to find a solution when googling - most likely I just can't get the wording right - I get loads of stuff but none of it really suits what i'm after.... HELP!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No Subject | Excel Worksheet Functions | |||
No Subject | Excel Worksheet Functions | |||
No Subject | Excel Worksheet Functions | |||
No Subject | Charts and Charting in Excel | |||
No Subject | Excel Discussion (Misc queries) |