VBA - Move onto next if does not start with

Balmer07

New Member
Joined
Feb 14, 2018
Messages
45
Office Version
  1. 365
Hi,

I am trying to get right code for the below scenario:

If the data in the cell does not start with "FGG-" then I want to move onto the row below.
If the data in cell does start with "FGG-2 then I want to do a Vloopup
I want to do this until the last row with data.

Can someone help?

thanks,
Steven
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Steven

You don't mention which cells/columns/sheets are involved so this code is for column A on Sheet1.
Code:
Dim cl As Range

    For Each cl In Sheets("Sheet1").Range("A1", Sheets("A" &  Rows.Count).End(xlUp)).Cells

        If cl.Value Like "FGG-*" Then
            ' do the lookup
        End If

    Next cl
 
Upvote 0
Hi,

This doesn't seem to work, I am maybe not putting the right detail into the code.
The first cell which I need to check is C3 and the vlookup would be going into cell D3, then I need it to replicate this until the last row, I trust this makes sense?

Thanks,
Steven
 
Upvote 0
Steven

The code I posted is for column A on Sheet1 starting in row 1.

For column C starting in row 3 you could try something like this.
Code:
Dim cl As Range
Dim Res As Variant

    For Each cl In Sheets("Sheet1").Range("C3", Sheets("C" &  Rows.Count).End(xlUp)).Cells

        If cl.Value Like "FGG-*" Then
            ' do the lookup
            Res = Application.VLookup(cl, Sheets("Sheet2").Range("A:B"), 2,0) ' this is a dummy lookup, you'll need to replace it with your own
      
            ' if lookup was successful put result in column D
            If Not IsError(Res) Then
                cl.Offset(,1).Value = Res
            End If
        End If

    Next cl
 
Upvote 0
Thanks for posting again

When I get to the first line of code it brings up an error?

Before this line the cell currently selected is "A1", does this make any difference?

I am quite new to VBA so trying to get my head around all of this

thanks,
 
Upvote 0
There's a major typo in the code.:eek:

Try this correction.
Code:
Dim cl As Range
Dim Res As Variant

    For Each cl In Sheets("Sheet1").Range("C3", Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp)).Cells

        If cl.Value Like "FGG-*" Then
            ' do the lookup
            Res = Application.VLookup(cl, Sheets("Sheet2").Range("A:B"), 2, 0) ' this is a dummy lookup, you'll need to replace it with your own
      
            ' if lookup was successful put result in column D
            If Not IsError(Res) Then
                cl.Offset(, 1).Value = Res
            End If
        End If

    Next cl
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top