Posts by shloksurya

shloksurya · Oct 15th 2016

Hi I want to enter some data into webpage text box, the view source code is as below
basically i am trying to automate website through vba
i tried below

<span class="leftbox3"><input type="text" name="cardNumber" maxlength="19" size="20" value=""
----------------------------------------------------------------------------------------------------------------------
ie.document.getElementsByClassName("leftbox3")(0).value = "123"
IE.Document.getElementsByName("cardNumber")(0).Value = "123"

No Luck please help

Regards,
Thanks in advance,
Please help
&amp;amp;quot;[email protected]&amp;amp;quot;[/email]

shloksurya · Sep 23rd 2016

I want to click on IE website having save button, using vba below is the source code for save button

thanks its bit urgent

thanks in advance

shloksurya · Sep 23rd 2016

Re: Click on save button on webpage through excel vba

ANY Update on this ?????????????:nana:

shloksurya · Sep 20th 2016

I want to click on web page save button in vba below is the source code for save button

thanks its bit urgent

thanks in advance

shloksurya · Sep 19th 2016

Re: Web Automation using VBA

i figured it out thanks for you help now i want to click on save button below is the code i want it to work in excel vba

HTML

<button id="upd" class="save" onMouseDown="submitBtnAndDisable('true','MERCH_MAINTAIN_DETAILS','upd')" name="upd" type="button">Save</button>

shloksurya · Sep 19th 2016

Hi,

I have a website

1] I need to enter the data into the website site fields through excel vba.
2] i am stuck at combo box, there are few combo box which should pick the data from excel cell
3] I am able to do the same with text boxes but i am not able figure out the combo box i have the code for combo box as below.

Code

Dim obj As Object




   For Each obj In IE.document.getElementsByTagName("select")(1).Options
    obj.Selected = True
    If obj.Selected = (UCase(obj.innerText) = UCase(ThisWorkbook.Sheets("RawData").range("E4" & 4).Value)) Then
    y = obj.Value
    Exit For
    Else
    y = y
    End If
    Next obj
    y = 0

Display More

But this does not work i have IE 11 version andexce2010

Please help me with this thanks in advance.

shloksurya · Oct 3rd 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

Code

Sub insert_egg_modifier_group_name()On Error GoTo ErrorNote
 Dim cell As Range
 Dim rng As Range
                            If Sheets("Items").Range("J1000").End(xlUp) Like "*Egg*" And Sheets("Sheet2").Range("A1000").End(xlUp) Like "*Egg*" Then
                            Exit Sub
                            Else
                            Dim copySheet As Worksheet
                            Dim pasteSheet As Worksheet
                            Set copySheet = Worksheets("EggSteak")
                            Set pasteSheet = Worksheets("Sheet2")
                            copySheet.Range("A2:C7").Copy
                            NextRow = Sheet2.Range("C" & Rows.Count).End(xlUp).Row
                            Sheet2.Activate
                            Sheet2.Range("A" & NextRow + 1).Select
                            ActiveSheet.Paste
                            Application.CutCopyMode = False
                            Application.ScreenUpdating = True
            End If
           ' End IfApplication.DisplayAlerts = False
 Application.DisplayAlerts = True
 Exit Sub
 ErrorNote:
 MsgBox ("Error No 2")
 End Sub

Display More

shloksurya · Oct 3rd 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

What is wrong in below code?

if sheet (Items) contains "Egg"
and Sheet2 also contains Egg
then Sheet 3 must display some data
But what here its happening is that there are more than 2 to 3 times the egg word, so it displays the data in sheet3 2 to 3 times I want it to display only once.

Code

Sub insert_egg_modifier_group_name()
On Error GoTo ErrorNote
Dim cell As Range
Dim rng As Range
                            If Sheets("Items").Range("J1000").End(xlUp) Like "*Egg*" And Sheets("Sheet2").Range("A1000").End(xlUp) Like "*Egg*" Then
                            Exit Sub
                            Else
                            Dim copySheet As Worksheet
                            Dim pasteSheet As Worksheet
                            Set copySheet = Worksheets("EggSteak")
                            Set pasteSheet = Worksheets("Sheet2")
                            copySheet.Range("A2:C7").Copy
                            NextRow = Sheet2.Range("C" & Rows.Count).End(xlUp).Row
                            Sheet2.Activate
                            Sheet2.Range("A" & NextRow + 1).Select
                            ActiveSheet.Paste
                            Application.CutCopyMode = False
                            Application.ScreenUpdating = True
            End If
           ' End If
            
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Exit Sub
ErrorNote:
MsgBox ("Error No 2")
End Sub

Display More

shloksurya · Sep 24th 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

FIND THE ATTACHMENT CODE IS WRITTEN IN SHEET1

Code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Surya
Dim rngCheck As Range
Set rngCheck = Intersect(Target, Range("B2:B500"))
If Not rngCheck Is Nothing Then
'Application.EnableEvents = False
                Select Case Target.Value
                Case "Egg" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Egg Mode
                If MsgBox("Do You want to Enter Egg Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Egg Mod"
                End If
                Case "Eggs" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Eggs Mode
                If MsgBox("Do You want to Enter Eggs Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Egg Mod"
                End If
                Case "Lamb Steak" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Lamb Steak
                If MsgBox("Do You want to Enter Lamb Steak Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Top Sirlon" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Top Sirlon
                If MsgBox("Do You want to Enter Top Sirlon Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Filet Mignon" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Filet Mignon
                If MsgBox("Do You want to Enter Filet Mignon Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Ribeye" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Ribeye
                If MsgBox("Do You want to Enter Ribeye Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Porterhouse" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Porterhouse
                If MsgBox("Do You want to Enter Porterhouse Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If


                Case "Strip Loin" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Strip Loin
                If MsgBox("Do You want to Enter Strip Loin Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Top Loin" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Top Loin
                If MsgBox("Do You want to Enter Top Loin Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Club" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Club
                If MsgBox("Do You want to Enter Club Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Flank" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Flank
                If MsgBox("Do You want to Enter Flank Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Skirt" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Skirt
                If MsgBox("Do You want to Enter Skirt Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
        
                Case "Sirloin" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Sirloin
                If MsgBox("Do You want to Enter Sirloin Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Chateaubriand" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Chateaubriand
                If MsgBox("Do You want to Enter Chateaubriand Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Delmonico" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Delmonico
                If MsgBox("Do You want to Enter Delmonico Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Flatiron" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Flatiron
                If MsgBox("Do You want to Enter Flatiron Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Hanger" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Hanger
                If MsgBox("Do You want to Enter Hanger Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                
                Case "London Broil" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''London Broil
                If MsgBox("Do You want to Enter London Broil Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "New York Strip" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''New York Strip
                If MsgBox("Do You want to Enter New York Strip Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Prime Rib" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Prime Rib
                If MsgBox("Do You want to Enter Prime Rib Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Tri -Tip" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Tri -Tip
                If MsgBox("Do You want to Enter Tri -Tip Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Steak Mod"
                End If
                Case "Steak & Eggs" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Steak & Eggs
                If MsgBox("Do You want to Enter Steak & Eggs Mod?", vbYesNo + vbQuestion) = vbNo Then
                Else
                ActiveCell.Offset(0, 7).Value = "Egg Mod"
                ActiveCell.Offset(1, 7).Value = "Steak Mod"
                End If
 


Application.EnableEvents = True
End Select
End If
Surya:
End Sub
'Egg
'Eggs
'Lamb Steak
'Top Sirlon
'Filet Mignon
'Ribeye
'Tenderlion
'T -Bone
'Porterhouse
'Strip Loin
'Top Loin
'Club
'Flank
'Skirt
'Sirloin
'Chateaubriand
'Delmonico
'Flatiron
'Hanger
'London Broil
'New York Strip
'Prime Rib
'Tri -Tip
'Steak & Eggs

Display More

shloksurya · Sep 24th 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

with all you respect & Smallman,

He had provided me the code was a part and i did reply that which did not help my need, right now i have written the above which i posted few hrs earlier it gave me error Procedure too large then i wrote below code ...

Code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Surya
Dim rngCheck As Range
  Set rngCheck = Intersect(Target, Range("B2:B100"))
    If Not rngCheck Is Nothing Then
            Application.EnableEvents = False
     
        Select Case Target.Value
            
            Case "Egg" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Egg Mode
            If MsgBox("Do You want to Enter Egg Mod?", vbYesNo + vbQuestion) = vbNo Then
            Else
            ActiveCell.Offset(0, 7).Value = "Egg Mod"
            End If
                                         
            Case "Eggs" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Eggs Mode
            If MsgBox("Do You want to Enter Eggs Mod?", vbYesNo + vbQuestion) = vbNo Then
            Else
            ActiveCell.Offset(0, 7).Value = "Egg Mod"
            End If
            
            Case "Lamb Steak" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Lamb Steak
            If MsgBox("Do You want to Enter Lamb Steak Mod?", vbYesNo + vbQuestion) = vbNo Then
            Else
            ActiveCell.Offset(0, 7).Value = "Steak Mod"
            End If
    
            Case "Top Sirlon" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Top Sirlon
            If MsgBox("Do You want to Enter Top Sirlon Mod?", vbYesNo + vbQuestion) = vbNo Then
            Else
            ActiveCell.Offset(0, 7).Value = "Steak Mod"
            End If
    
    Application.EnableEvents = True
End Select
End If
Surya:
End Sub

Display More

-----------------------------------------------------------------------------
But still it doesnot run after the first case.

Thanks for all your help and support.

shloksurya · Sep 24th 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

Hi myarshi29,

Thanks for reply i had wrote below code, Please check the output.

In Sheet1

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:B300")) Is Nothing Then
Call CheckAvailability
End If
End Sub

In Module Form

Code

Sub CheckAvailability()
'---------------------------------------------------------------------------------------------------------------------------------B2
'Egg
If Sheet1.Range("B2").Value = "Egg" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Eggs Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo A
ActiveCell.Offset(0, 7).Value = "Egg Mod"
End If
A:
'Eggs
If Sheet1.Range("B2").Value = "Eggs" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Eggs Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo B
ActiveCell.Offset(0, 7).Value = "Egg Mod"
End If
B:
'Lamb Steak
If Sheet1.Range("B2").Value = "Lamb Steak" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo C
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
C:
'Top Sirlon
If Sheet1.Range("B2").Value = "Top Sirlon" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo E
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
E:
'Filet Mignon
If Sheet1.Range("B2").Value = "Filet Mignon" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo F
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
F:
'Ribeye
If Sheet1.Range("B2").Value = "Ribeye" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo G
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
G:


'Tenderlion
If Sheet1.Range("B2").Value = "Tenderlion" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo H
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
H:
'T -Bone
If Sheet1.Range("B2").Value = "T-Bone*" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo I
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
I:
'Porterhouse
If Sheet1.Range("B2").Value = "Porterhouse" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo j
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
j:
'Strip Loin
If Sheet1.Range("B2").Value = "Strip Loin" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo k
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
k:
'Top Loin
If Sheet1.Range("B2").Value = "Top Loin" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo l
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
l:
'Club
If Sheet1.Range("B2").Value = "Club" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo M
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
M:


'Skirt
If Sheet1.Range("B2").Value = "Skirt" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo N
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
N:
'Sirloin
If Sheet1.Range("B2").Value = "Sirloin" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo O
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
O:


'Chateaubriand
If Sheet1.Range("B2").Value = "Chateaubriand" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo p
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
p:


'Delmonico
If Sheet1.Range("B2").Value = "Delmonico" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo q
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
q:
'Flatiron
If Sheet1.Range("B2").Value = "Flatiron" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo r
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
r:
'Hanger
If Sheet1.Range("B2").Value = "Hanger" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo s
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
s:
'London Broil
If Sheet1.Range("B2").Value = "London Broil" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo t
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
t:


'New York Strip
If Sheet1.Range("B2").Value = "New York Strip" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo u
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
u:


'Prime Rib
If Sheet1.Range("B2").Value = "Prime Rib" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo v
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
v:
'Tri -Tip
If Sheet1.Range("B2").Value = "Tri -Tip" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo w
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
w:
'Steak & Eggs
If Sheet1.Range("B2").Value = "Steak & Eggs" And Sheet1.Range("J2").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo X
ActiveCell.Offset(0, 7).Value = "Egg Mod"
ActiveCell.Offset(1, 7).Value = "Steak Mod"
End If
X:
'---------------------------------------------------------------------------------------------------------------------------------B3
'Egg
If Sheet1.Range("B3").Value = "Egg" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Eggs Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo A3
ActiveCell.Offset(0, 7).Value = "Egg Mod"
End If
A3:
'Eggs
If Sheet1.Range("B3").Value = "Eggs" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Eggs Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo B3
ActiveCell.Offset(0, 7).Value = "Egg Mod"
End If
B3:
'Lamb Steak
If Sheet1.Range("B3").Value = "Lamb Steak" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo C311
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
C311:
'Top Sirlon
If Sheet1.Range("B3").Value = "Top Sirlon" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo E3
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
E3:
'Filet Mignon
If Sheet1.Range("B3").Value = "Filet Mignon" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo F4
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
F4:
'Ribeye
If Sheet1.Range("B3").Value = "Ribeye" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo G5
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
G5:


'Tenderlion
If Sheet1.Range("B3").Value = "Tenderlion" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo H6
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
H6:
'T -Bone
If Sheet1.Range("B3").Value = "T-Bone*" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo I7
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
I7:
'Porterhouse
If Sheet1.Range("B3").Value = "Porterhouse" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo j8
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
j8:
'Strip Loin
If Sheet1.Range("B3").Value = "Strip Loin" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo k9
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
k9:
'Top Loin
If Sheet1.Range("B3").Value = "Top Loin" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo l10
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
l10:
'Club
If Sheet1.Range("B3").Value = "Club" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo M11
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
M11:


'Skirt
If Sheet1.Range("B3").Value = "Skirt" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo N12
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
N12:
'Sirloin
If Sheet1.Range("B3").Value = "Sirloin" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo O13
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
O13:


'Chateaubriand
If Sheet1.Range("B3").Value = "Chateaubriand" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo p14
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
p14:


'Delmonico
If Sheet1.Range("B3").Value = "Delmonico" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo q161
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
q161:
'Flatiron
If Sheet1.Range("B3").Value = "Flatiron" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo r17
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
r17:
'Hanger
If Sheet1.Range("B3").Value = "Hanger" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo s18
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
s18:
'London Broil
If Sheet1.Range("B3").Value = "London Broil" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo t19
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
t19:


'New York Strip
If Sheet1.Range("B3").Value = "New York Strip" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo u20
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
u20:


'Prime Rib
If Sheet1.Range("B3").Value = "Prime Rib" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo v21
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
v21:
'Tri -Tip
If Sheet1.Range("B3").Value = "Tri -Tip" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo w22
ActiveCell.Offset(0, 7).Value = "Steak Mod"
End If
w22:
'Steak & Eggs
If Sheet1.Range("B3").Value = "Steak & Eggs" And Sheet1.Range("J3").Value = "" Then
    If MsgBox("Do You want to Enter Steak Mod?", vbYesNo + vbQuestion) = vbNo Then GoTo X23
ActiveCell.Offset(0, 7).Value = "Egg Mod"
ActiveCell.Offset(1, 7).Value = "Steak Mod"
End If
X23:
'--------------------------------------------------------------------------------------------------------
'So on Till Range B300

Display More

' Can any one help me to shorten this code.

thanks
Suryakant

shloksurya · Sep 23rd 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

hello Guies,

Can some one help me here Please

Thanks a lot in advance

shloksurya · Sep 9th 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

Hi Smallman, & Other Team Members,
Please help, i will explain my problem again

I have Sheet1 and Sheet2.

1] I type "AAA" in column A1 of Sheet1.
2] It should go and search the List which contains AAA in sheet 2 and copy the Adjucent column(B) next to the result found in below case if AAA is searched the result is "Veg"
3] It would be great if it give message if its non veg.

Thanks Please help

Sheet1

[TABLE="width: 500"]

[tr]

[td]

A - Column

[/td]

[td]

B - Column

[/td]

[/tr]

[tr]

[td]

AAA

[/td]

[td]

Veg

[/td]

[/tr]

[tr]

[td]

CCC

[/td]

[td][/td]

[/tr]

[tr]

[td]

BBB

[/td]

[td][/td]

[/tr]

[tr]

[td]

DDD

[/td]

[td][/td]

[/tr]

[/TABLE]

Sheet2
[TABLE="width: 500"]

[tr]

[td]

A - Column

[/td]

[td]

B - Column

[/td]

[/tr]

[tr]

[td]

AAA

[/td]

[td]

Veg

[/td]

[/tr]

[tr]

[td]

BBB

[/td]

[td]

Non Veg

[/td]

[/tr]

[tr]

[td]

CCC

[/td]

[td]

Veg

[/td]

[/tr]

[tr]

[td]

AAA

[/td]

[td]

Non Veg

[/td]

[/tr]

[tr]

[td]

BBB

[/td]

[td]

Non Veg

[/td]

[/tr]

[/TABLE]

shloksurya · Sep 5th 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

Hi Smallman,

I am using something like below,

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    With Sheets("Sheet2").Range("B2:B" & Range("B1048576").End(xlUp).Row)
    
        Set Rng = .Find(What:=Sheets("Sheet1").Range("B1048576").End(xlUp), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Application.EnableEvents = False
        If Rng Is Nothing Then
            MsgBox "Not found"
        Else
            Sheets("Sheet1").Range("B1048576").End(xlUp).Offset(0, 8) = Sheets("Sheet2").Cells(Rng.Row, Rng.Column + 1)
        End If
    End With
    Application.EnableEvents = True
End Sub

Display More

shloksurya · Sep 5th 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

Hi adygelber,

Using above I get Zero in column J, no luck.

Thanks any ways.

Please help and one can ............

shloksurya · Sep 4th 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

Hi Smallman,

2^15 --> used-in-lookup-to-find-last-cell-containing-a-specific-value
------------------------------------------------------------------

Your Above code is great , just i need the out put that is Egg mod or steak mod result in Column J of Sheet Items.

Thanks waiting for your Reply

shloksurya · Sep 4th 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

Hi AdrianB.

Little bit short need to work more, Please find the excel as attachment. With details.
I am waiting for reply
thanks
Surya

shloksurya · Sep 3rd 2015

Re: Find the text in Columns("C:C") and compare the list in next sheet

Hi AdrianB,
Thanks for quick reply we are very close I need to find the below list in
Set Rng = .Range("B" & i).Find(What:="Egg","Apple","GGG","HHH","Food"

Also I am running code in getting error message -- Run time error Application-define or object-define error

Private Sub Worksheet_Change(ByVal Target As Range)-----------------------------------> so that as soon as user enter any text like "Egg","Apple" and hits tab it should give message saying its Egg mode or saying veg mode if Yes then it should enter Veg Mod/Egg mod as below

Code

For i = 2 To Sheets("Sheet1").Range("B1048576").End(xlUp).Row
    With Sheets("Sheet1")
        Set Rng = .Range("B" & i).Find(What:="Egg", _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Rng Is Nothing Then
            Sheets("Sheet1").Range("C" & i) = "Veg Mod"
        Else
            Sheets("Sheet1").Range("C" & i) = "Egg Mod"
        End If
    End With
Next I




End Sub

Display More

---------------------------------------------------------------------------------------------------------------------------------------------------------
let me explain you once again

I have list consider "Egg","Eggs","AAA & Eggs","Meat","Apple","SoftDrinks","etc".

if user enter any one of the above item in Sheet1.Column"B" then column "J" in sheet1 should have text "Egg Mode" in same row where he has entered Eggs.
like

Items ----- Description
Eggs----------- Egg mod
Egg------------ Egg mod
AAA & Eggs --- Egg mod
Meat ----------- Steak mod
Apple ---------- Veg mod

Thanks a lot for you quick reply I am waiting for this
thanks
AdrianB

shloksurya · Sep 3rd 2015

As soon as I type Eggs in column B2 and hit tab MsgBox("Do You want to Enter Egg Mode?", vbYesNo + vbQuestion) then it should check in sheet2 for Eggs same goes with B4,B5 and so on I can write any thing from the list it should compare in Sheet2 column A2if found then it should enter the text as non veg against the egg in column J it can be duplicate also in column B like eggs in B3 as well as eggs in B17Also attached the sheet name sample for reff.....thankyou,surya