Select Cell based on Value in other cell 2

[Palmcrest]

I have a row of data in row 1 from C:1 to C:55
Each cell in row 1 has a number from 1 to 52 starting at C:1

Cell A:1 has the current week ie 31 (using weeknum formula)

I would like to use VBA to look at A:1 and end up selecting the right cell in row c based on the A:1 value

I have searched and googled but I cant quite find what I need

Thanks for reading this and I hope you can help

 

[Andrzejek]

How about:

Dim i As Integer

i = 1
Do Until Range("A1").Value = Range("C" & i).Value
    i = i + 1
Loop

Range("C" & i).Select

MsgBox "Cell A1 = Cell C" & i

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

How about

Cells(range("A1").value, "C").Select

 

[Andrzejek]

And now to 'split a hair'
"I have a [column] of data [] from C:1 to C:55
Each cell in [column C] has a number from 1 to 52 starting at C:1"

From C1 to C55 – 55 cells with numbers 1 to 52, so what do we have in the remaining 3 cells: C53, C54, and C55?

Skip’s way selects cell C31 (because value in A1 is 31) no matter what the value in C31 is. What if the values in C1-C55 (?) are not consecutive...?

Too much time on my hands today...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Deniall]

It would have helped if the OP had defined what he means by "the right cell".[ ] Right because of its value, or right because of its position?

 

[Palmcrest]

Hi guys
Firstly thankyou for your feedback
Secondly , I apologise as I realised I have asked the wrong question (Very tired late at night)

I meant to say

I have a value in A:1 (current week)
I have numbers 1 - 60 in C:1 to AH:1

If its week 31 I want to use this value to select week 31 along row 1

The numbers 1 - 60 on row one will not change.

Once again thank you and my apologies for getting it wrong with the first post

 

[Deniall]

Skip's solution works fine (after correcting for your confusion between rows and columns):

Sub Fred()
Cells(1, Range("A1").Value + 2).Select
End Sub

It can be made more flexible by assigning a named range to your C1:AH1 range (noting the correct use of the colon within a range rather than within a single cell address), then referencing the named range within the VBA thus:

Sub Fred()
Cells(Range("MyVals")(1, 1).Row, Range("MyVals")(1, 1).Column + Range("A1").Value - 1).Select
End Sub

And you can make it even more flexible by assigning another named range to your cell A1.

Splitting another hair, you actually muddied the waters by telling us that cells C1:AH1 contained the numbers 1-60.[ ] Unless I am missing something that information was an irrelevant distraction.

 

[Palmcrest]

Thanks for that
Works great - in terms of muddie waters its all part of the learning curve, thanks for your feedback.

 

[Deniall]

De nada.

 

[Andrzejek]

Welcome to Tek Tips, Palmcrest

Please use "Great Post? Star it" link in the helpful post(s) to award a star for helpful post. This way others see which post you found beneficial, and it says: 'Thank you' to whoever helped you.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

I'm curious. So what's the underlying reason to SELECT this particular cell?

 

[Palmcrest]

A:1 had the week number
C1 to ??60 had numbers 1 to 52
I wanted to land on the current week to populate cells further down with formulas using auto fill.
Thanks for your input

 

[SkipVought]

So you're planning to auto fill manually?

 

[Palmcrest]

No... I used fill down with offset to fill the data programatically. prob better ways but it works for me now