#1   Report Post  
Old December 17th 07, 01:07 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2007
Posts: 3
Default 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   Report Post  
Old December 18th 07, 11:30 AM posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default 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   Report Post  
Old December 18th 07, 02:02 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,624
Default

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   Report Post  
Old December 18th 07, 11:09 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2007
Posts: 3
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
No Subject Excel Worksheet Functions 0 April 5th 05 12:00 AM
No Subject Excel Worksheet Functions 0 March 22nd 05 01:00 AM
No Subject Excel Worksheet Functions 0 March 4th 05 01:00 AM
No Subject Charts and Charting in Excel 0 February 18th 05 01:00 AM
No Subject Excel Discussion (Misc queries) 0 January 24th 05 01:00 AM


All times are GMT +1. The time now is 07:40 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017