PDA

View Full Version : Solved: Select Collumn based on Cell Value



nickirvine
12-23-2010, 03:17 AM
Hi,

Wonder if you can help me.

I'm trying to get a piece of VB code or a formula to add 1 to the figure in a cell. The cell it select and adds the figure to is based on the end result of a formula.

I.e
in one cell I have the collumn number and one cell I have the row number.

So Cell C1 on my sheet is the collumn letter so the value of C1 is "A",
Cell C2 is the Row number so the value of C2 is "4"

Is there a formula which will select the info from "C1" and "C2" and add one to the figure in "A4".

The worksheet is protected so I can't provide a physical example but that should be all the information you need.

Any help would be really appreciated.

Thansk,

Nick

Bob Phillips
12-23-2010, 03:35 AM
I can't see a formula doing it, but maybe a VBA worksheet event. But you would need some rules, if C1 holds A and you change it to B, do you automatically add 1 to B4, or would you clear C2 when C1 gets entered and only add 1 when C2 is entered and C1 is not blank? That sort of thing needs to be considered.

nickirvine
12-23-2010, 03:58 AM
Yeah ideally would need a little check to ensure both C1 and C2 are filled.

I was thinking of having the current value displayed then the user has to press a command button to add the value to field. This command button could trigger a VB event.

Any help on what the VB event would be?

Thanks,

Nick

Bob Phillips
12-23-2010, 04:14 AM
If it was a command button, it wouldn't be an event. It would just be a simple macro assigned to the button, something like



Sub AddOne()
If Range("C1").Value <> "" And Range("C2").Value <> "" Then

With Range(Range("C1").Value & Range("C2").Value))

.Value = .Value + 1
End With
End If
End Sub

nickirvine
12-23-2010, 04:37 AM
Thanks that really helpful and works a treat.

Another little thing, how can I get it to add to a different sheet? I've tried doing

Sub AddOne()
If Range("Sheet4.C1").Value <> "" And Range("Sheet4.C2").Value <> "" Then

With Range(Range("Sheet4.C1").Value & Range("Sheet4.C2").Value)

.Value = .Value + 1
End With
End If
End Sub

But had no luck. I'm sure this is a simple change somewhere.

Thanks again

Bob Phillips
12-23-2010, 04:39 AM
Sub AddOne()
If Range("Sheet4.C1").Value <> "" And Range("Sheet4.C2").Value <> "" Then

With Worksheets("SomeOtherSheet").Range(Range("Sheet4.C1").Value & Range("Sheet4.C2").Value)

.Value = .Value + 1
End With
End If
End Sub

nickirvine
12-23-2010, 05:03 AM
Thanks loads for your help.