offset cell position when there is value in a selected cell
 

Hi guys,

what i have is date in A1 to G1. In the following row, A2 to G2 i have
data on the amount of inventory that is in the store. If there exist a
value in any of the cell in B1 to B10. Let say there is a vaule 20 in
cell D2. This value 0f 20 will be place in cell offset from cell D2 by
a amount of offset entered by the user. Let say offset amount is 3. The

value of 20 will be automatically copied to cell A3(offset 1 row below
D2 to D3 and 3 column to the right of D3 to A3). Anyone could point me
to how this can be accomplish?


Befo


A B C D E
1 MON TUE WED THUR FRI


2 20


3


After:


A B C D E
1 MON TUE WED THUR FRI


2 20


3 20 <<<<<<<<Being offset from cell D2 by an offset value of
3


After:


A B C D E
1 MON TUE WED THUR FRI


2 20


3 20 <<<<<<<<offset value = 1

offset cell position when there is value in a selected cell
 

Range("A1").Offset(user_value-1).Value = Range("D2").Value

Is it meant to be dynamic, that is working out which ell in A2:G2 to copy to
A3, and what if there is more than one value in that range?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kuansheng" wrote in message
ups.com...
Hi guys,

what i have is date in A1 to G1. In the following row, A2 to G2 i have
data on the amount of inventory that is in the store. If there exist a
value in any of the cell in B1 to B10. Let say there is a vaule 20 in
cell D2. This value 0f 20 will be place in cell offset from cell D2 by
a amount of offset entered by the user. Let say offset amount is 3. The

value of 20 will be automatically copied to cell A3(offset 1 row below
D2 to D3 and 3 column to the right of D3 to A3). Anyone could point me
to how this can be accomplish?


Befo


A B C D E
1 MON TUE WED THUR FRI


2 20


3


After:


A B C D E
1 MON TUE WED THUR FRI


2 20


3 20 <<<<<<<<Being offset from cell D2 by an offset value of
3


After:


A B C D E
1 MON TUE WED THUR FRI


2 20


3 20 <<<<<<<<offset value = 1

offset cell position when there is value in a selected cell
 

Its meant to be dynamic. When ever the is value in any of the cell
withthin the range A2:G2 for example. There will be an offset, the
amount of offset is determine be the user.

offset cell position when there is value in a selected cell
 

Range("A2:G2").Offset(user_value-1).Value = Range("M2").Value

offsets by row

Range("A2:G2").Offset(,user_value-1).Value = Range("M2").Value

offsets by column

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kuansheng" wrote in message
oups.com...
Its meant to be dynamic. When ever the is value in any of the cell
withthin the range A2:G2 for example. There will be an offset, the
amount of offset is determine be the user.

offset cell position when there is value in a selected cell
 

Hi Bob,

Thanks for your advice. I m quite new to VBA. Could you tell how how to
get it to work. Do i place the code in a module?and for the user-value
portion can i replace it with a cell location?

offset cell position when there is value in a selected cell
 

If you want to replace it with a cell location, you might want to use a
worksheet change event to trap when that cell changes and automatically
trigger the copy. lik this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "M1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Range("A2:G2").Offset(Target.Value).Value = Range("M2").Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kuansheng" wrote in message
ps.com...
Hi Bob,

Thanks for your advice. I m quite new to VBA. Could you tell how how to
get it to work. Do i place the code in a module?and for the user-value
portion can i replace it with a cell location?

offset cell position when there is value in a selected cell
 

Hi Bob i tried to follow the instruction. It seems that nothing is
happening. I guess i am doing it all worng.

I have in cell A22 a numeric value this gives the offset value. I have
in cell B22 a value that is to be copied to the new cell location that
is being offset.

When ever there is a negative value in any of the cell in the range
E25:DW25. Let say cell G25 now contain -25 and the offset value is 2.
What it needs to do is that the value that is in B22 will be copied to
the cell 1 row below and 2 column to the left of cell G25.

It is possible for more than 1 cell within E25:DW25 to have a negative
value. And the process is the smae for all case.

All these are dynamic. Could you help me out, i am a real idiot in
this. So sorry.