incomplete IF case macro

T

Tonyk1051

Board Regular
Joined
Feb 1, 2021
Messages
132
Office Version
  1. 2019
Platform
  1. Windows
Hi,

heres the link to the spreadsheet OB1.xlsm
Below is the code i put together and it works just that I dont know what to add to make it work exactly as I want

Issue 1:for the shure code in column AD there are only 2 words SHUR and SHUR20 the code puts the results I want for both when i just want it for the keyword SHUR not SHUR20, what do i need to add to the code to make it do that?

Issue 2: The rule for FUJI PHOTO FILM U.S.A.,INC. (located in column D) if column N has keywords “LENSES-Mirrorless Lenses” or “LENSES-Mirrorless Lenses” and column W is open box and column O is over $500 put in column H used, the code looks right to me at least but it doesnt excute the code, what am I doing wrong?

Issue 3: D & H DISTRIBUTING CO, the code works but when it comes to Microsoft, I only need it to apply the results only when the keywords in column N says "COMPUTER-Notebooks"


Code: 
Sub Openbox()

Dim cel As Range, Rng As Range, Vendor As String

Worksheets("Not on a Category").Activate

Range("D2").Select

Set Rng = Range(Selection, Selection.End(xlDown))

For Each cel In Rng

Vendor = cel.Text

Select Case Vendor

Case "TIFFEN MANUFACTURING CORP."

If InStr(1, cel.Offset(, 2), "steadicam", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "lowel", 1) <> 0 Then

If cel.Offset(, 19) = "Open Box" Then cel.Offset(, 4) = "used"

End If



Case "SHURE INCORPORATED"

If InStr(1, cel.Offset(, 19), "Open Box", 1) <> 0 And _

InStr(1, cel.Offset(, 26), "SHUR", 1) <> 0 Then

cel.Offset(, 4) = "used"

End If



Case "TECH DATA CORP."

If InStr(1, cel.Offset(, 2), "viewsonic", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "dell", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "LG", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "HP", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "Apple", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "sony", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "asus", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "Beats by Dr. Dre", 1) <> 0 Then

If cel.Offset(, 19) = "Open Box" Then cel.Offset(, 4) = "used"

End If



Case "D & H DISTRIBUTING CO."

If InStr(1, cel.Offset(, 2), "asus", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "dell", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "Lenovo", 1) <> 0 Or _

InStr(1, cel.Offset(, 2), "Microsoft", 1) <> 0 Then

If cel.Offset(, 19) = "Open Box" Then cel.Offset(, 4) = "used"

End If



Case "FUJIFILM"

If InStr(1, cel.Offset(, 10), "Mirrorless Lenses", 1) <> 0 Or _

InStr(1, cel.Offset(, 10), "Mirrorless Cameras", 1) <> 0 Then

If cel.Offset(, 11) > 500 And cel.Offset(, 19) = "Open box" Then

cel.Offset(, 2) = "used"

End If

End If

'etcetera

End Select

cel.Offset(, 4).Interior.Color = vbGreen 'So you can see what already is done

Next cel



End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
For issue 1, change this
VBA Code: 
InStr(1, cel.Offset(, 26), "SHUR", 1) <> 0 Then

TO

VBA Code: 
cel.Offset(, 26).value = "SHUR" Then
Issue 2
Is it "open Box" OR "Open Box"........Jusr before the start of the SUB, insert the line Option Compare Text...see if that solves the issue
 
Upvote 0
Also, why are you using If Instr....when there is only a single entry in the specified column, eg
VBA Code: 
Case "ALMO CORPORATION"
            If InStr(1, cel.Offset(, 2), "Barco", 1) <> 0 Then
                If cel.Offset(, 19) = "Open Box" Then cel.Offset(, 4) = "used"
                End If
could be

VBA Code: 
Case "ALMO CORPORATION"
            If  cel.Offset(, 2) = "Barco" Then
                If cel.Offset(, 19) = "Open Box" Then cel.Offset(, 4) = "used"
                End If
You can apply this to ALL cases where there is only a single text in the cell
 
Upvote 0
Michael M said:
Also, why are you using If Instr....when there is only a single entry in the specified column, eg
VBA Code: 
Case "ALMO CORPORATION"
            If InStr(1, cel.Offset(, 2), "Barco", 1) <> 0 Then
                If cel.Offset(, 19) = "Open Box" Then cel.Offset(, 4) = "used"
                End If
could be

VBA Code: 
Case "ALMO CORPORATION"
            If  cel.Offset(, 2) = "Barco" Then
                If cel.Offset(, 19) = "Open Box" Then cel.Offset(, 4) = "used"
                End If
You can apply this to ALL cases where there is only a single text in the cell
Click to expand...
im just learning from past examples from all the help ive been given and it works so bear with me, i'll try your way and let you know, thanks
 
Upvote 0
Ok, no worries.....I thought I might have been missing something !!
 
Upvote 0
Michael M said:
Also, why are you using If Instr....when there is only a single entry in the specified column, eg
VBA Code: 
Case "ALMO CORPORATION"
            If InStr(1, cel.Offset(, 2), "Barco", 1) <> 0 Then
                If cel.Offset(, 19) = "Open Box" Then cel.Offset(, 4) = "used"
                End If
could be

VBA Code: 
Case "ALMO CORPORATION"
            If  cel.Offset(, 2) = "Barco" Then
                If cel.Offset(, 19) = "Open Box" Then cel.Offset(, 4) = "used"
                End If
You can apply this to ALL cases where there is only a single text in the cell
Click to expand...
issue 1 is solved and the shorterning of the codes works but the FUJI code still doesnt work any idea what im doing wrong?

Code: 
 Case "FUJI PHOTO FILM U.S.A.,INC."

If InStr(1, cel.Offset(, 10), "LENSES-Mirrorless Lenses", 1) <> 0 Or _

InStr(1, cel.Offset(, 10), "DIGITAL CAMERAS-Mirrorless Cameras", 1) <> 0 Then

If cel.Offset(, 11) > 500 And cel.Offset(, 19) = "Open Box" Then

cel.Offset(, 2) = "used"

End If

End If

'etcetera

End Select

cel.Offset(, 4).Interior.Color = vbGreen 'So you can see what already is done

Next cel



End Sub
 
Upvote 0
Shouldn't the FUJIFILM case actually be
FUJI PHOTO FILM U.S.A.,INC.
VBA Code: 
Case "FUJI PHOTO FILM U.S.A.,INC."
            If cel.Offset(, 11) = "Mirrorless Lenses" Or _
                cel.Offset(, 11) = "Mirrorless Cameras" Then
                If cel.Offset(, 12) > 500 And cel.Offset(, 20) = "Open box" Then
                    cel.Offset(, 2) = "used"
                End If
            End If
        'etcetera
    End Select
AND...I think you need to check your offsets in that case as well....note the snippet above.
I think this Offset will need to be changed as well.....
VBA Code: 
                    cel.Offset(, 2) = "used"
 
Upvote 0
Michael M said:
Shouldn't the FUJIFILM case actually be
FUJI PHOTO FILM U.S.A.,INC.
VBA Code: 
Case "FUJI PHOTO FILM U.S.A.,INC."
            If cel.Offset(, 11) = "Mirrorless Lenses" Or _
                cel.Offset(, 11) = "Mirrorless Cameras" Then
                If cel.Offset(, 12) > 500 And cel.Offset(, 20) = "Open box" Then
                    cel.Offset(, 2) = "used"
                End If
            End If
        'etcetera
    End Select
AND...I think you need to check your offsets in that case as well....note the snippet above.
I think this Offset will need to be changed as well.....
VBA Code: 
                    cel.Offset(, 2) = "used"
Click to expand...
yea i notice the case name was incorrect but even after changing it to the correct name it still didnt work, im going to double check the offset like you said
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Listbox multiple column advice
2
Replies
10
Views
610
ipbr21054
I
E
  • Question Question
Change Log With VBA
2
Replies
19
Views
850
Joe4
Joe4
N
  • Solved
Copy/paste a row from within a loop, before that loop moves on to next cell
Replies
5
Views
520
Joe4
Joe4
T
  • Solved
VBA Macro to sort table by vendor copy table into email body and send, then do the same for the next vendor in the table to the last vendor
Replies
5
Views
1K
takingiwhanau
T
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
250
mumps
mumps

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

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