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

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

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

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