Loop - Find substring, Change cell value

[Aardvark1971]

Hi,

First post here (glad I found this place!).

I'm converting an extremely important Quattro Pro spreadsheet to Excel after 20+ years!

Running into a lot of things that may seem quite simple to some of you - but the syntax and expressions are different from Quattro to Excel and I'm struggling.

I have macros that I need to rebuild in Excel (for Mac 2011, vers. 14.4).

This is the first one I can't figure out. I'll express it in simple English first:

a) Within a DO loop, check incremented cell value for a substring.

b) If substring not found, change cell values to blank (blank incremented cells A, B).


Loop needs to check a range of cells (D24:D308).

Here's a simplified expression (syntax is very incorrect):

If (Ordering!D24<>"My Value",Ordering!A24:b24="", otherwise do nothing).

I have figured out how to use the Range statement to change a cell value, but not with an incremented cell pointer.

I don't know how to use the Dim and Integer variables correctly within the IF statement.

I got this far:

Dim i as Integer
i = 24
Do Until i > 308

missing IF statement goes here

i = i + 1
Loop


Probably not much of a challenge, but my IF syntax attempts crash the compiler.


Any help at all is much appreciated!

[MarvinP]

Hey Aardvark,

Instead of a Do Until loop how about a For Next loop.

Dim is to declare variables for type in the beginning of a subroutine...

Something like this?

Dim CellCtr as Double

For CellCtr = 24 to 308
If Cells(CellCtr, "D") = <substring?>
your code in here...
Next CellCtr

The above is just a piece of code that will loop and go through all the cells...

Hope this helps.

[Aardvark1971]

Thank you! That's new to me!

But I still don't understand how to use the pointer incrementer. And what about the substring check? "My Value" is just the substring of cell D.

This is incomplete:

Dim CellCtr As Double
For CellCtr = 24 To 308
If Cell(CellCtr, "D") <> "My Value" Then
Range("Sheetname!CellCtr) = ""
'This needs to change cells A and B to blank (using the pointer)
End If
Next CellCtr

[MarvinP]

Ok - somewhat closer....

If you want to do stuff on another sheet you need to use this kind of code

Worksheets("OtherName").Cells(CellCtr,"D).

We really need to see a sample of what you have and what you want to get to. You can attach a sample workbook by clicking on "Go Advanced" and then the Paper Clip Icon above the advanced message area.

[Aardvark1971]

Thank you.

I should point out that I'm not working on another sheet in the example given. Good coding habits generally mean you specify the sheet name, which is why it was shown in the example.

It is how to use the pointer, within an IF statement (and now a Range statement) that I'm having trouble with. And checking for substring (using the same cell pointer) on cell D "Company K".

I have attached a sample worksheet as suggested.

Whls button is supposed to run RD_Whls macro.

Desired Macro function:

If "Company M" substring in cells D24:D308 is not found, then for each row this is true, change cells A and B to "" (erase Item No. and Qty).

This is the code I have that does not work:

Sub RD_Whls()
'
' RD_Whls Macro

Dim CellCtr As Double
For CellCtr = 24 To 308
If Cells(CellCtr, "D") <> "Company M" Then
'Range("Ordering!CellCtr,A) = ""
End If
Next CellCtr

End Sub

[MarvinP]

OK,

See if this makes sense. I needed to use InStr to see if a string is contained in another.

Sub RD_Whls()
'
' RD_Whls Macro
Dim LastDRow As Double
Dim CellCtr As Double
Dim strSearch As String

LastDRow = Cells(Rows.Count, "D").End(xlUp).Row
'Using the above instead of 308 you can have more or less rows

strSearch = "Company M"

'strSearch = InputBox("What string doesn't get zero?")
'Use the above line to allow Input of strSearch

For CellCtr = 24 To LastDRow
    If InStr(Cells(CellCtr, "D"), strSearch) = 0 Then
        Cells(CellCtr, "A") = ""
        Cells(CellCtr, "B") = ""
    End If
Next CellCtr

End Sub

See attached with code..

[Aardvark1971]

Kudos to you.

It blew up if there was a empty row(s) and then another populated row, but sorting first took care of that.

LastDRow = Cells(Rows.Count, "D").End(xlUp).Row

Does this command mean "Count total number of rows, minus 1"?

If so, how does it "know" when to stop? Must be a value in cell D? And if so, is this why it went into an infinite loop if there were embedded blank rows?

[Aardvark1971]

Oops - sorting did not solve the infinite loop issue. Sorry about that.

Column D24:d308 actually contains a formula and I'm wondering if it's failing because of this. This is the formula in column D:

=IF($A24<>"",VLOOKUP(UPPER($A24),'Macintosh HD:Users:Home:Documents:[Database7.xlsx]Database'!$B$2:$E$2731,4,FALSE),"")

Coming from the Quattro world, we'd use a VALUE qualifier to ensure it was checking the value of the cell, not the formula, when the macro ran.

Is something like this going on here and causing it to run in an infinite loop?

[MarvinP]

I guess I need a sample of the one that has a problem. Then I'd see how to program around it.

In Excel do a Ctrl down arrow. It is exactly the same as Cells(activecell.row, activecell.column).End(xlDown).

When you get to the bottom of a worksheet do a ctrl Up arrow. See where it goes. Where it stops is the LastRow.
If you want to get to the row just down from there you'd do LastRow + 1

[Aardvark1971]

hmmm. My sample that I created did not blow up on the sort...

I sent you a private message.