Hi ,

I have a noob question, and I guess is not that hard thing, just my head
is not functioning right now properly and I cant figure it out.
I have 2 columns of data.
1st column has cells with values and empty cells
second column has multiple rows corresponding to each value in the
column in the right.
Something like that:

A B

1 US region1
2 region2
3 region3
4 region4
5 ..(other regions)
6
7 EU region1
8 region2
9 region3

(All this is a pivot table)


My problem is to rite a formula, that says, based on the value in column
"B", offset to the left and go UP as many times as needed, until you
reach non-blank cell, and return the value of this nonblank cell...
so if i say take "B3" which will be region3, offset to left will be
"A3", then go up as many times as needed, until encounter nonblank cell,
in this case will be cell A1, and return the value of A1

other example: I take B8, which is region2, offset to left, to A8 , go
as many times as needed, until encounter first nonblank cell, which in
this case will be A7 "EU", and return the value of A7, and do not go any
further UP

need that to incorporate in larger getpivotdata formula

Nested if would be one solution, but number of regions is every time
different and might be quite a long list. So my idea was to offset to
the right and loop until find a nonblank cell... But I am still learning
VBA, so I need a little help with writing this function procedure.

Any ideas?

Thank you

Sub ColAValues()
Dim selectcell As Range
On Error Resume Next
Set selectcell = Application.InputBox("Select a cell in Column B", _
Default:=ActiveCell.Address, Type:=8)
If selectcell.Address = "" Then Exit Sub
If selectcell.Offset(, -1) < "" Then
foundit = selectcell.Offset(, -1).Value
Else
foundit = selectcell.Offset(, -1).End(xlUp).Value
End If
MsgBox (foundit)
End Sub

HTH,
--
Data Hog


"SK" wrote:

Hi ,

I have a noob question, and I guess is not that hard thing, just my head
is not functioning right now properly and I cant figure it out.
I have 2 columns of data.
1st column has cells with values and empty cells
second column has multiple rows corresponding to each value in the
column in the right.
Something like that:

A B

1 US region1
2 region2
3 region3
4 region4
5 ..(other regions)
6
7 EU region1
8 region2
9 region3

(All this is a pivot table)


My problem is to rite a formula, that says, based on the value in column
"B", offset to the left and go UP as many times as needed, until you
reach non-blank cell, and return the value of this nonblank cell...
so if i say take "B3" which will be region3, offset to left will be
"A3", then go up as many times as needed, until encounter nonblank cell,
in this case will be cell A1, and return the value of A1

other example: I take B8, which is region2, offset to left, to A8 , go
as many times as needed, until encounter first nonblank cell, which in
this case will be A7 "EU", and return the value of A7, and do not go any
further UP

need that to incorporate in larger getpivotdata formula

Nested if would be one solution, but number of regions is every time
different and might be quite a long list. So my idea was to offset to
the right and loop until find a nonblank cell... But I am still learning
VBA, so I need a little help with writing this function procedure.

Any ideas?

Thank you
.

Here is the code for a function.

Function FindHeader(rge As Range)
If rge.Offset(, -1).Value < "" Then
FindHeader = rge.Offset(, -1)
Else
FindHeader = rge.Offset(, -1).End(xlUp)
End If
End Function

HTH,
--
Data Hog




I have a noob question, and I guess is not that hard thing, just my head
is not functioning right now properly and I cant figure it out.
I have 2 columns of data.
1st column has cells with values and empty cells
second column has multiple rows corresponding to each value in the
column in the right.
Something like that:

A B

1 US region1
2 region2
3 region3
4 region4
5 ..(other regions)
6
7 EU region1
8 region2
9 region3

(All this is a pivot table)


My problem is to rite a formula, that says, based on the value in column
"B", offset to the left and go UP as many times as needed, until you
reach non-blank cell, and return the value of this nonblank cell...
so if i say take "B3" which will be region3, offset to left will be
"A3", then go up as many times as needed, until encounter nonblank cell,
in this case will be cell A1, and return the value of A1

other example: I take B8, which is region2, offset to left, to A8 , go
as many times as needed, until encounter first nonblank cell, which in
this case will be A7 "EU", and return the value of A7, and do not go any
further UP

need that to incorporate in larger getpivotdata formula

Nested if would be one solution, but number of regions is every time
different and might be quite a long list. So my idea was to offset to
the right and loop until find a nonblank cell... But I am still learning
VBA, so I need a little help with writing this function procedure.

Any ideas?

Thank you
.