+ Reply to Thread
Results 1 to 3 of 3

lookup macro

Hybrid View

tkollo1984 lookup macro 02-15-2011, 01:02 PM
stnkynts Re: lookup macro 02-15-2011, 02:05 PM
tkollo1984 Re: lookup macro 02-15-2011, 02:48 PM
  1. #1
    Registered User
    Join Date
    02-15-2011
    Location
    Budapest
    MS-Off Ver
    Excel 2007
    Posts
    10

    lookup macro

    Hi I need to make a macro that does the following:

    1. Looks up on the active workshet in a table (A4:I25) if value in column "I" equals to "3", if yes copies value in column "B" from the same row into another table in the same worksheet (let's say B130:B140),.

    2. Do the same, but if value in column "I" equals to "1" and copy column "B" to B142:B152

    I am fairly new with vba and macros so I have many difficulties with this, could you help me out?

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: lookup macro

    I am still a little groggy so if i missed something let me know.
    Option Explicit
    
    Sub Find1or3()
    Dim icell As Integer, Count As Integer, icell2 As Integer
    Dim x As String, y As String
    
    'err handling incase Row 130:140 are full ie Row 140 has a value
    If IsEmpty(ActiveSheet.Range("B140")) Then
        'nothing
        Else
            x = MsgBox("Are you sure Range B130:B140 is empty?", vbYesNo)
                If x = vbYes Then
                    GoTo 1
                ElseIf x = vbNo Then
                    Exit Sub
                End If
    End If
    1:
    If IsEmpty(ActiveSheet.Range("B152")) Then
        'nothing
        Else
            y = MsgBox("Are you sure Range B142:B152 is empty?", vbYesNo)
                If y = vbYes Then
                    GoTo 2
                ElseIf y = vbNo Then
                    Exit Sub
                End If
    End If
    2:
    
    'utilized counter so we could fill a specific range only
    Count = 0
    For icell = 4 To 25
        If ActiveSheet.Range("I" & icell).Value = "3" Then
            ActiveSheet.Range("B" & icell).Copy
            ActiveSheet.Range("B130").Offset(Count, 0).PasteSpecial xlPasteAll
            Count = Count + 1
        End If
    Next icell
    
    'used multiple loops cause counter gets a little messy with multiple if statements
    Count = 0
    For icell2 = 4 To 25
        If ActiveSheet.Range("I" & icell2).Value = "1" Then
            ActiveSheet.Range("B" & icell2).Copy
            ActiveSheet.Range("B142").Offset(Count, 0).PasteSpecial xlPasteAll
            Count = Count + 1
        End If
    Next icell2
    
    End Sub

  3. #3
    Registered User
    Join Date
    02-15-2011
    Location
    Budapest
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: lookup macro

    Thanks a lot, works like a charm, now I am off to decipher it for later use.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1