Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell reference from previous index function
I am using the index function to find a value in a table and once I find this
value I need my macro to return the cell info one column beside it. eg. Offset(0,1) I am having a very hard time setting returning a range for cell because it is a variable. I am no programmer so could someone please get me on the right track. Thank you. outing = 1 For turn = 1 To 4 fpress = Application.Index(wksCopyBrake.Range("A3:IU20"), _ Application.Match(turn, wksCopyBrake.Range("A3:A20"), 0), _ Application.Match(outing, wksCopyBrake.Range("A3:IU3"), 0)) rngPasteBrake.Value = fpress rngPasteBrake.Font.Size = 8 This is where I need the "rpress" value which is always located Offset( 0,1) from "fpress" rngPasteRBrake.Value = rpress rngPasteRBrake.Font.Size = 8 Set rngPasteBrake = rngPasteBrake.Offset(0, 1) Set rngPasteRBrake = rngPasteRBrake.Offset(0, 1) Next turn Set rngPasteBrake = rngPasteBrake.Offset(1, -4) Set rngPasteRBrake = rngPasteRBrake.Offset(1, -4) outing = outing + 1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell reference from previous index function
I think I'd break it down into smaller pieces.
Although, I'm confused at what all the ranges are and what the worksheets refer to, this may get you closer: Option Explicit Sub testme() Dim ColMatch As Variant Dim RowMatch As Variant Dim myRng As Range Dim Outing As Long Dim fPress As Range Dim rPress As Range Dim Turn As Long Dim wksCopyBrake As Worksheet Dim RngPasteBrake As Range Set RngPasteBrake = Worksheets("sheet2").Range("a1") Set wksCopyBrake = ActiveSheet 'Worksheets("sheet1") With wksCopyBrake Set myRng = .Range("a3:Iu20") Outing = 1 For Turn = 1 To 4 RowMatch = Application.Match(Turn, myRng.Columns(1), 0) ColMatch = Application.Match(Outing, myRng.Rows(1), 0) If IsError(RowMatch) _ Or IsError(ColMatch) Then 'what should happen if there's an error Else Set fPress = myRng(RowMatch, ColMatch) fPress.Select Set rPress = fPress.Offset(0, 1) With RngPasteBrake .Value = fPress.Value 'rpress.value '??? .Font.Size = 8 End With End If Next Turn End With End Sub Notice that I changed the fPress and rPress to range variables--not simple values. xadnora wrote: I am using the index function to find a value in a table and once I find this value I need my macro to return the cell info one column beside it. eg. Offset(0,1) I am having a very hard time setting returning a range for cell because it is a variable. I am no programmer so could someone please get me on the right track. Thank you. outing = 1 For turn = 1 To 4 fpress = Application.Index(wksCopyBrake.Range("A3:IU20"), _ Application.Match(turn, wksCopyBrake.Range("A3:A20"), 0), _ Application.Match(outing, wksCopyBrake.Range("A3:IU3"), 0)) rngPasteBrake.Value = fpress rngPasteBrake.Font.Size = 8 This is where I need the "rpress" value which is always located Offset( 0,1) from "fpress" rngPasteRBrake.Value = rpress rngPasteRBrake.Font.Size = 8 Set rngPasteBrake = rngPasteBrake.Offset(0, 1) Set rngPasteRBrake = rngPasteRBrake.Offset(0, 1) Next turn Set rngPasteBrake = rngPasteBrake.Offset(1, -4) Set rngPasteRBrake = rngPasteRBrake.Offset(1, -4) outing = outing + 1 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell reference from previous index function
Dave,
With a little editing I had it up and running in about 15 minutes.....Thank you so much. I really don't know what I would do without all you guys!!! Dax "Dave Peterson" wrote: I think I'd break it down into smaller pieces. Although, I'm confused at what all the ranges are and what the worksheets refer to, this may get you closer: Option Explicit Sub testme() Dim ColMatch As Variant Dim RowMatch As Variant Dim myRng As Range Dim Outing As Long Dim fPress As Range Dim rPress As Range Dim Turn As Long Dim wksCopyBrake As Worksheet Dim RngPasteBrake As Range Set RngPasteBrake = Worksheets("sheet2").Range("a1") Set wksCopyBrake = ActiveSheet 'Worksheets("sheet1") With wksCopyBrake Set myRng = .Range("a3:Iu20") Outing = 1 For Turn = 1 To 4 RowMatch = Application.Match(Turn, myRng.Columns(1), 0) ColMatch = Application.Match(Outing, myRng.Rows(1), 0) If IsError(RowMatch) _ Or IsError(ColMatch) Then 'what should happen if there's an error Else Set fPress = myRng(RowMatch, ColMatch) fPress.Select Set rPress = fPress.Offset(0, 1) With RngPasteBrake .Value = fPress.Value 'rpress.value '??? .Font.Size = 8 End With End If Next Turn End With End Sub Notice that I changed the fPress and rPress to range variables--not simple values. xadnora wrote: I am using the index function to find a value in a table and once I find this value I need my macro to return the cell info one column beside it. eg. Offset(0,1) I am having a very hard time setting returning a range for cell because it is a variable. I am no programmer so could someone please get me on the right track. Thank you. outing = 1 For turn = 1 To 4 fpress = Application.Index(wksCopyBrake.Range("A3:IU20"), _ Application.Match(turn, wksCopyBrake.Range("A3:A20"), 0), _ Application.Match(outing, wksCopyBrake.Range("A3:IU3"), 0)) rngPasteBrake.Value = fpress rngPasteBrake.Font.Size = 8 This is where I need the "rpress" value which is always located Offset( 0,1) from "fpress" rngPasteRBrake.Value = rpress rngPasteRBrake.Font.Size = 8 Set rngPasteBrake = rngPasteBrake.Offset(0, 1) Set rngPasteRBrake = rngPasteRBrake.Offset(0, 1) Next turn Set rngPasteBrake = rngPasteBrake.Offset(1, -4) Set rngPasteRBrake = rngPasteRBrake.Offset(1, -4) outing = outing + 1 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell reference from previous index function
Glad it got you started.
xadnora wrote: Dave, With a little editing I had it up and running in about 15 minutes.....Thank you so much. I really don't know what I would do without all you guys!!! Dax "Dave Peterson" wrote: I think I'd break it down into smaller pieces. Although, I'm confused at what all the ranges are and what the worksheets refer to, this may get you closer: Option Explicit Sub testme() Dim ColMatch As Variant Dim RowMatch As Variant Dim myRng As Range Dim Outing As Long Dim fPress As Range Dim rPress As Range Dim Turn As Long Dim wksCopyBrake As Worksheet Dim RngPasteBrake As Range Set RngPasteBrake = Worksheets("sheet2").Range("a1") Set wksCopyBrake = ActiveSheet 'Worksheets("sheet1") With wksCopyBrake Set myRng = .Range("a3:Iu20") Outing = 1 For Turn = 1 To 4 RowMatch = Application.Match(Turn, myRng.Columns(1), 0) ColMatch = Application.Match(Outing, myRng.Rows(1), 0) If IsError(RowMatch) _ Or IsError(ColMatch) Then 'what should happen if there's an error Else Set fPress = myRng(RowMatch, ColMatch) fPress.Select Set rPress = fPress.Offset(0, 1) With RngPasteBrake .Value = fPress.Value 'rpress.value '??? .Font.Size = 8 End With End If Next Turn End With End Sub Notice that I changed the fPress and rPress to range variables--not simple values. xadnora wrote: I am using the index function to find a value in a table and once I find this value I need my macro to return the cell info one column beside it. eg. Offset(0,1) I am having a very hard time setting returning a range for cell because it is a variable. I am no programmer so could someone please get me on the right track. Thank you. outing = 1 For turn = 1 To 4 fpress = Application.Index(wksCopyBrake.Range("A3:IU20"), _ Application.Match(turn, wksCopyBrake.Range("A3:A20"), 0), _ Application.Match(outing, wksCopyBrake.Range("A3:IU3"), 0)) rngPasteBrake.Value = fpress rngPasteBrake.Font.Size = 8 This is where I need the "rpress" value which is always located Offset( 0,1) from "fpress" rngPasteRBrake.Value = rpress rngPasteRBrake.Font.Size = 8 Set rngPasteBrake = rngPasteBrake.Offset(0, 1) Set rngPasteRBrake = rngPasteRBrake.Offset(0, 1) Next turn Set rngPasteBrake = rngPasteBrake.Offset(1, -4) Set rngPasteRBrake = rngPasteRBrake.Offset(1, -4) outing = outing + 1 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reference the same cell in all previous sheets? | Excel Discussion (Misc queries) | |||
How can I reference a cell on a previous worksheet | Excel Discussion (Misc queries) | |||
Reference Previous Worksheet Built-in Function | Excel Worksheet Functions | |||
reference to previous cell (always) | Excel Discussion (Misc queries) | |||
Now With Index -- IF Function Does Not Work With Cell Reference | Excel Worksheet Functions |