Trouble with Search,Select,Insert Macros - HELP!

[newatmacros]

Hi Everyone,

As you can tell by my username, I am very new to Excel VBA. I just started really utilizing it when I first realized that I can record a macro; however, as I continued it, realized what a powerful tool it can be for my work place when utilizing various data sheets. So far, many of the blogs posted and the reviews attached to them have been very helpful and have helped me create some great macros that do exactly what I want them to do; however, there is one command that is stumping me.
I need a macro do the following:

Search for certain cells with multiple criteria options. (ie. Beverages, Produce, Cold Food)
Select those cells.
Then, select entire columns of the selected cells.
Then, select Blank cells in the columns selected.
Lastly, enter "0" for all of the blank cells in those columns only.

It is very important that the code can search through the entire worksheet (A:ZZ) for these cells with this criteria as I do not want it dependent that the criteria cells will be in the same locations. Also, it is important that only those blank cells from the specified columns are filled with "0". If any other cells are filled with zero, the entire computations that come after will be skewed.
I have tried constant macros and "If Else Then" macros but I have never gotten any of them to fully work for what I need. If anyone knows how to do this with an "If Else Then" macro, it can be very helpful for other macros I may need to create so I can know what I may doing wrong.
Help?

Thank everyone!

 

[oldbrewer]

if you run through A1:zz1000 and if any key word (s) are present store the cell location in an array.

now tell me what you want to do with those cells - presumably paste them in a column....

 

[oldbrewer]

if cells(1,1)="apple" then cells(1,2)="apple found" else cells(1,2) = "not found"

 

[Fishboy]

Hi Everyone,

As you can tell by my username, I am very new to Excel VBA. I just started really utilizing it when I first realized that I can record a macro; however, as I continued it, realized what a powerful tool it can be for my work place when utilizing various data sheets. So far, many of the blogs posted and the reviews attached to them have been very helpful and have helped me create some great macros that do exactly what I want them to do; however, there is one command that is stumping me.
I need a macro do the following:

Search for certain cells with multiple criteria options. (ie. Beverages, Produce, Cold Food)
Select those cells.
Then, select entire columns of the selected cells.
Then, select Blank cells in the columns selected.
Lastly, enter "0" for all of the blank cells in those columns only.

It is very important that the code can search through the entire worksheet (A:ZZ) for these cells with this criteria as I do not want it dependent that the criteria cells will be in the same locations. Also, it is important that only those blank cells from the specified columns are filled with "0". If any other cells are filled with zero, the entire computations that come after will be skewed.
I have tried constant macros and "If Else Then" macros but I have never gotten any of them to fully work for what I need. If anyone knows how to do this with an "If Else Then" macro, it can be very helpful for other macros I may need to create so I can know what I may doing wrong.
Help?

Thank everyone!

Hi newatmacros, welcome to the boards.

If I have understood correctly, then try the following macro in a COPY of your workbook:

Sub UpdateColumns()
' Defines variables
Dim LastRow As Long, LastCol As Long
Dim Cell As Range, cRange As Range, dRange As Range


' Defines LastCol as the last column of data based on the first row
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
' Sets the check range as A1 to the last column of data on thefirst row
Set cRange = Range("A1", Cells(1, LastCol))
' For each cell in the check range
For Each Cell In cRange
    ' Defines LastRow as the last row of data for the current column
    LastRow = ActiveSheet.Cells(Rows.Count, Cell.Column).End(xlUp).Row
    ' If the count of how many times the current column contains any of the 3 criteria is greater than zero then...
    If Application.WorksheetFunction.CountIf(Range(Cells(1, Cell.Column), Cells(LastRow, Cell.Column)), "Beverages") > 0 _
        Or Application.WorksheetFunction.CountIf(Range(Cells(1, Cell.Column), Cells(LastRow, Cell.Column)), "Produce") > 0 _
        Or Application.WorksheetFunction.CountIf(Range(Cells(1, Cell.Column), Cells(LastRow, Cell.Column)), "Cold Food") > 0 Then
            ' Fill the blank cells in that column with a zero
            Range(Cells(1, Cell.Column), Cells(LastRow, Cell.Column)).SpecialCells(xlCellTypeBlanks).Value = 0
    End If
' Check next cell in check range
Next Cell
    
End Sub

 

[newatmacros]

Hi oldbrewer,

Thank you for taking the time to look over my post and for helping clarify the "IfThenElse" macro a little bit more for me! I appreciate it!

 

[newatmacros]

Hi oldbrewer,

Thank you for taking the time to look over my post and for helping clarify the "IfThenElse" macro a little bit more for me! I appreciate it!

 

[newatmacros]

Hi Fishboy,

Wow, thank you for writing a macro code that I can try for my data set! I appreciate it!
I tried the VBA with a copy of my worksheet; however, an error of "400" keeps coming up. Do you happen to know why this may be the case?

 

[Fishboy]

Ah, I suspect it does that if it doesn't find any blanks. I'll need to tweak my code but am currently replying from my phone

 

[Fishboy]

Sorry for the delay in my response, but I am only just getting back to a computer this morning.

As I suspected, the error occurs when no blanks are found in a column causing this line of code to throw a wobbly:

            Range(Cells(1, Cell.Column), Cells(LastRow, Cell.Column)).SpecialCells(xlCellTypeBlanks).Value = 0

I believe I have resolved the issue with the amended code below. I have highlighted my change in bold red:

Sub UpdateColumns()
' Defines variables
Dim LastRow As Long, LastCol As Long
Dim Cell As Range, cRange As Range, dRange As Range




' Defines LastCol as the last column of data based on the first row
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
' Sets the check range as A1 to the last column of data on thefirst row
Set cRange = Range("A1", Cells(1, LastCol))
' For each cell in the check range
For Each Cell In cRange
    ' Defines LastRow as the last row of data for the current column
    LastRow = ActiveSheet.Cells(Rows.Count, Cell.Column).End(xlUp).Row
    ' If the count of how many times the current column contains any of the 3 criteria is greater than zero then...
    If Application.WorksheetFunction.CountIf(Range(Cells(1, Cell.Column), Cells(LastRow, Cell.Column)), "Beverages") > 0 _
        Or Application.WorksheetFunction.CountIf(Range(Cells(1, Cell.Column), Cells(LastRow, Cell.Column)), "Produce") > 0 _
        Or Application.WorksheetFunction.CountIf(Range(Cells(1, Cell.Column), Cells(LastRow, Cell.Column)), "Cold Food") > 0 Then
            ' Fill the blank cells in that column with a zero
            On Error Resume Next
            Range(Cells(1, Cell.Column), Cells(LastRow, Cell.Column)).SpecialCells(xlCellTypeBlanks).Value = 0
    End If
' Check next cell in check range
Next Cell
    
End Sub

 

[newatmacros]

Hi Fishboy,
No worries about the lateness of your response. I was not able to get to my worksheet till this week anyways. i just sincerely appreciate your help!
I tried the macro with the correction and it works like a charm, filling blank cells in each column specified; however, for each column the filling of the blank cells just stops randomly. Each stopping point is not concurrent with the other. I counted the filled cells in each column to see if there was a pattern of when the macro stops filling the blank cells; however, there is no pattern. One column can have 80 filled cells while another can only have 20 filled cells.
I looked over the macro and see that you instructed it to do so until the last row; therefore, I am unsure why this may be the case. Any thoughts?
Again, thank you for your help!