Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Position selected cell | Setting up and Configuration of Excel | |||
automatic offset cell location when there is a value in a selected cell. | Excel Worksheet Functions | |||
Default cursor/selected cell position after protecting | Excel Worksheet Functions | |||
How to I return the position of a selected cell in a range? | Excel Programming | |||
Returning Position Points from the currently selected cell | Excel Programming |