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

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,

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
>

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