offset cell position when there is value in a selected cell

[kuansheng]

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?


Before:


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

 

[Bob Phillips]

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]

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.

 

[Bob Phillips]

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]

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?

 

[Bob Phillips]

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]

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
E25W25. 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 E25W25 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.