v lookup

  • Thread starter Thread starter shanaron
  • Start date Start date
S

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.
 
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.
 
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 -
Click to expand...

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.
 
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
 
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
Click to expand...
 
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.
 
Back
Top