HELP! Trying to search a range for a value and return 1 or 0 on a list

[greywolfnsl]

So I am very new to excel and I have been trying to search a range for a word and return a 1 or zero. Then I need it to loop through the list that will vary in size. I have I keep getting errors. I have been at this for many hours banging my head at it.

Here is where the stuff is at:
The "Calendar" Sheet has the text in range B4:N34
The "Work Sheet Grocery List" Column A has the text I am trying to match it to
The "Work Sheet Grocery List" Column B is where I am trying to return the 1 or 0 if column A text is in the Calendar range
So if the Calendar has Pudding in cell D8, I want the other sheet to put a 1 in column B of the corresponding A column. Also "Pudding will be in Column A numerous times.

Here is what I have so far: (I have tried strings and arrays)

Sub GroceryListPart1()

' Get the last row with text
Dim LastRow As Long
'this get the last row EVEN IF there is a break in the center. Starts at the bottom and works it way up
LastRow = Worksheets("Grocery List").Cells(Rows.Count, 1).End(xlUp).Row
Debug.Print LastRow

'Dim Calendar As Range
'Set Calendar = Worksheets("calendar").Range("A1:A8")
Dim calendarrange As String
Dim Calendarsheet As String
Dim calendar(0 To 27)



calendarrange = "B4:N34"
Calendarsheet = "Calendar"


Set calendar = Worksheets(Calendarsheet).Range(calendarrange)


Dim i As Long, Total As Long
' Use LastRow in loop
For i = 2 To LastRow

If InStr(calendar, Worksheets("Grocery List").Cells(i, 1)) Then
Worksheets("Grocery List").Cells(i, 2) = 1
Else
Worksheets("Grocery List").Cells(i, 2) = 0
End If
Next i


End Sub

Thank you for any help!

 

[Teeroy]

Is there a particular reason for using VBA? This could be managed by a formula in Column B of "Work Sheet Grocery List".

B2: =IF(COUNTIF(Calendar!$B$4:$N$34,"="&A2)>0,1,0)

 

[greywolfnsl]

I have everything already done with formulas right now in the book. I'm mainly doing it for two reason. I'm trying to learn VBA and this seemed like it should of been the easiest one to solve starting out but its turning out to be more of a challenge than I thought. And also, with the formula only book, I have over 60,000 forumlas in the book so it takes about a min to run and right now there isn't much to the book so I want to speed everything up using VBA.

And just an update, I am scratched the one from above and am trying a new one:

Sub GroceryListPart1B()
' Get the last row with text
Dim LastRow As Long
'this get the last row EVEN IF there is a break in the center. Starts at the bottom and works it way up
LastRow = Worksheets("Grocery List").Cells(Rows.Count, 1).End(xlUp).Row
Debug.Print LastRow

'For Loop
Dim I As Long, Ingrident As String
Dim Calendar As String


'Calendar search
Dim c As Long
Dim r As Long

For I = 2 To LastRow
For r = 4 To 34
For c = 2 To 14
Ingrident = Worksheets("Grocery List").Range("A" & I)
Debug.Print Ingrident
Calendar = Worksheets("Calendar").Cellc(r, c)
Worksheets("Grocery List").Range("B" & I) = IIf(Ingrident = Calendar, "1", "0")
Next
Next
Next


End Sub

 

[Teeroy]

FYI VBA is always slower than formulas; there's a cost to a runtime compiled language.

You appear to be running too many loops in your check, and you have a typo in your calendar assignment .cells call. If you can provide some sample data and the expected output I can propose a simpler structure.

If you want to speed up VBA you can work in memory and minimise the interaction with the worksheet (i.e. no. of reads and writes). e.g.

Dim Temp 'creates variant
Temp = Range("A1:C4").value

Temp is now a 3 x 3 array that you can work with.

 

[greywolfnsl]

The overall goal is to make a Recipe Book that I am able to populate a calendar with the title of the recipes on the calendar. When those titles are on the calendar, then a grocery list of all the ingredients you need for the week will populate on the grocery list. Right now I'm just trying to do a check if the recipes are in the calendar.

The Calendar is your typical calendar Sun-Saturday. The Grocery list sheet has the following column. A) the Title of the recipe B) is suppose to be the 1 or 0 if the recipe title is in the grocery list. C,D, and E are the amount of each ingredient, unit of measurement, and Ingredients respectively.

An example is (with Bread and pudding in the calendar):

A B C D E
Bread 1 1 Cup Flour
Bread 1 1/2 Tsp Yeast
Cookie 0 1 Cup Flour
Cookie 0 2 Tbsp Vanilla Extract
Pudding 1 2 Cups Water
Pudding 1 1 Box pudding Mix
Stew 0 1 Lbs Chuck Roast
Stew 0 3 Each Carrots

Column B on the Grocery List shows a 1 if it is in the Calendar (Just as Bread and Pudding are) and a 0 if not on the calendar (as Cookie and Stew are not).

Thanks for the help.

PS the spaces wont keep for the columns but hopefully it still makes sense.