v lookup

[shanaron]

please help with instructions for dummies using v lookup.

I have data from row 130 to 8000 and across 48 colums. I need all the
columns data to be picked up.
I have row 2 to 129 that I want the data to end up in. I enter in b2
vlookup(a130,b130:aw8000,130.0) It will not work and I have only just
discovered v lookup and don't know what i am doing.

 

[Ragdyer]

Don't know if I quite follow exactly what you're trying to do.

Try this formula in B2:

=VLOOKUP($A130,$B$130:$AW$8000,COLUMNS($A:A),0)

Copy it across to AW2,
Then copy down to Row 129.

 

[shanaron]

Don't know if I quite follow exactly what you're trying to do.

Try this formula in B2:

=VLOOKUP($A130,$B$130:$AW$8000,COLUMNS($A:A),0)

Copy it across to AW2,
Then copy down to Row 129.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !






- Show quoted text -

no this didnt work

I have data that is 8000 employees in different sections. i am trying
to pull select employees out of the 8000 odd employees. All colums
from b to aw is required. I am using A column as the employees id
number.

rows 2 to 129 is where i want the data to end up so if it actually
worked i would type employee id number in a 2 and all the columns from
b2 to aw2 would come up with their data.

 

[David McRitchie]

Check out page
http://www.mvps.org/dmcritchie/excel/vlookup.htm
You will have to put formulas into each cell to pull in values
from an entire row of the table. The value to be matched
must be to the left of anything to be returned.

 

[Stephen]

Hi all small problem.
I have 3 ranges, 2 of those ranges I want to convert to UPPER case and one I
want to be proper case.

I have the UPPERCASE done how do I add the next range g5:g40 to make that
proper case... thanks see below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
On Error GoTo Err_Handler
Set rg = [j5:j40 , d5:d40] 'limit the range here
If Intersect(Target(1, 1), rg) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target(1, 1)
.Value = UCase(.Value)
End With
ExitHere:
Application.EnableEvents = True
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical, "Error " & Err.Number
Resume ExitHere
End Sub

 

[Dave Peterson]

One way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim RngUCase As Range
Dim RngProper As Range

Set RngUCase = Me.Range("j5:j40,d5:d40")
Set RngProper = Me.Range("g5:g40")

Set Target = Target.Cells(1) 'just one cell

On Error GoTo Err_Handler:

Application.EnableEvents = False
With Target
If .HasFormula Then
'skip it
ElseIf Not Intersect(.Cells, RngUCase) Is Nothing Then
'in the upper case range
.Value = StrConv(.Value, vbUpperCase)
ElseIf Not Intersect(.Cells, RngProper) Is Nothing Then
'in proper case range
.Value = StrConv(.Value, vbProperCase)
End If
End With

ExitHere:
Application.EnableEvents = True
Exit Sub

Err_Handler:
MsgBox Err.Description, vbCritical, "Error " & Err.Number
Resume ExitHere

End Sub

Hi all small problem.
I have 3 ranges, 2 of those ranges I want to convert to UPPER case and one I
want to be proper case.

I have the UPPERCASE done how do I add the next range g5:g40 to make that
proper case... thanks see below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
On Error GoTo Err_Handler
Set rg = [j5:j40 , d5:d40] 'limit the range here
If Intersect(Target(1, 1), rg) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target(1, 1)
.Value = UCase(.Value)
End With
ExitHere:
Application.EnableEvents = True
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical, "Error " & Err.Number
Resume ExitHere
End Sub

 

[Ragdyer]

I copied the lookup value cell location from your posted formula attempt.

Simply change the location to A2, and try this in B2:

=VLOOKUP($A2,$B$130:$AW$8000,COLUMNS($A:A),0)

Again, copy across to AW2,
Then copy down to Row129.