Vba "if"

Benzoli7 · Jul 11, 2011

Can anyone help with an IF statement in VBA that says if any cell in Column A contains a keyword, run a certain block of code. And then, if the keyword is not present, skip that block and continue the code.

Thanks.

hiker95 · Jul 11, 2011

Benzoli7,

There are a few ways we can search for the keywords.

Can we see some of your raw data?

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

328mike · Jul 11, 2011

Benzoli7 said:
Can anyone help with an IF statement in VBA that says if any cell in Column A contains a keyword, run a certain block of code. And then, if the keyword is not present, skip that block and continue the code.

Thanks.

If Range("A:A").Value = "YourValue" Then YourScript

Scott Huish · Jul 11, 2011

You can't compare a whole range to a value like that:

Code:

Sub test()
If Application.WorksheetFunction.CountIf(Range("A:A"), "YourValue") Then Call YourCode
End Sub
 
Sub YourCode()
MsgBox "Found It"
End Sub

vconfused · Jul 11, 2011

You may want to look at the Find Method. Straight from help:
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Call usbMyCode(c)
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Scott Huish · Jul 11, 2011

Why would you use FindNext if you only need to find 1 occurence?

Code:

Sub test2()
Dim c As Range
Set c = Range("A:A").Find("YourValue")
If Not c Is Nothing Then Call YourCode
End Sub
 
Sub YourCode()
MsgBox "Found It"
End Sub

vconfused · Jul 11, 2011

I read it as go through every cell and if the keyword is found then do something, but keep going through every cell.

Benzoli7 · Jul 12, 2011

Thanks for the responses. I will try to clarify a little bit.

 This macro compares new rate and update rate submissions from different vendors against existing rates and it makes recommendations whether to or not to contract the vendors. The worksheet “Rate Comparison” creates a unique identifier by concatenating several columns. This worksheet then looks for that unique identifier on the "Current Ranking" sheet. If it finds it, it places a 1 in column AA. If not, it places “New” in column AA. The macro then autofilters for the rows that contain “New”. If “New” exists, the macro adds the details from that row to the "Proposed Ranking" sheet so that the rate can be ranked/compared against the existing rates and a new rank can be assigned to all rates. The problem that I am having is that if there are no “New” unique identifiers, the macro bugs out. I would like to find a way to check for “New” unique identifiers and if none are found, skip the block of code that adds the data to the "Proposed Ranking" sheet. I have attached the section of code in which the above process operates. I know it isn’t the cleanest so if you see a better way to accomplish this, by all means, please recommend it.

Thank You all for your help.

HTML:

'Checks for new carrier lane
    Range("AA7").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(COUNTIF('Current Ranking'!C[-25],'Rate Comparison'!RC[-15])>0,1,""NEW"")"
    Range("AA7").AutoFill Destination:=Range("AA7:AA" & LR), Type:=xlFillDefault
     Calculate

    
'Adds new lanes to proposed ranking sheet

   '
   'I want to skip this section if "NEW" doesn't exist.
   '
   

   Sheets("Rate Comparison").Activate
    Range("A6").Select
    Range("A6").AutoFilter
    Range("A6").AutoFilter Field:=27, _
        Criteria1:="=New"
    Range("A7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("C" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("C7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("E" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("D7:J7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("F" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("Q7:R7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("Q" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("B7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("O" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("K7:L7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Sheets("Rate Comparison").Select
   Range("A6").AutoFilter

Vba "if"

Benzoli7

Board Regular

Excel Facts

hiker95

Well-known Member

328mike

New Member

Scott Huish

MrExcel MVP

vconfused

Well-known Member

Scott Huish

MrExcel MVP

vconfused

Well-known Member

Benzoli7

Board Regular

Similar threads

Forum statistics

Share this page

Vba "if"

Board Regular

Excel Facts

Well-known Member

New Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock