[RESOLVED] Easy question...probably has to do with arrays

[Bill-E-BoB]

Hey guys, I always code this the hard way...want to do it the easy way this time so I was hoping someone could let me in on the secret. Without knowing the right verbiage its always hard to find what I need doing a search.

I've got a spreadsheet in excel, and one of the columns has branch codes in it. For every row that has a branch code of AI, FI, FJ, etc etc (18 total branch codes) I need to delete it. The way I know how to do that is like this:

ROW = 2
WHILE NOT .CELLS(ROW,2).VALUE = ""
     BR_CODE = .CELLS(ROW,2).VALUE
       IF BR_CODE = "AI" THEN
          .ROWS(ROW).DELETE
       END IF
       IF BR_CODE = "FI" THEN
          .ROWS(ROW).DELETE
       END IF
       IF BR_CODE = "FJ" THEN
          .ROWS(ROW).DELETE
          ROW = ROW - 1
       END IF
'ETC. FOR ALL 18 BRANCHES.
ROW = ROW + 1
WEND

I normally just do it that way, because its quicker to copy & paste than it is to find the right answer. But I don't want my code to look sloppy anymore, so whats the right way? People talk about arrays all the time...I've never used one. Can someone show me an example as it would apply to this scenario?

*EDIT* that code wouldn't actually work quite right because it would skip a row after each one it deletes...but you get the idea of what I'm trying to do...

[Logophobic]

Either of these should work.

Dim BR_CODE As String
Dim Row As Integer
 
Row = 2
BR_CODE = Cells(Row, 2).Value
Do Until BR_CODE = ""
  Select Case BR_CODE
  Case "AI", "FI", "FJ"
    Rows(Row).Delete
  Case Else
    Row = Row + 1
  End Select
  BR_CODE = Cells(Row, 2).Value
Loop

Dim BR_CODE As String
Dim Row As Integer
Dim ALL_CODES As String
 
ALL_CODES = "AI,FI,FJ"
Row = 2
BR_CODE = Cells(Row, 2).Value
Do Until BR_CODE = ""
  If InStr(1, ALL_CODES, BR_CODE) > 0 Then
    Rows(Row).Delete
  Else
    Row = Row + 1
  End If
  BR_CODE = Cells(Row, 2).Value
Loop

[Bill-E-BoB]

Awsome, thanks! Just to make sure I'm understanding what goes on correctly (so that hopefully someday I can start answering questions on here instead of always just asking them...) tell me if I've got this down.

In your first example, the Case Code on line 8 basically says "if its blah, or blah, or blah, or blah, then do whatever's on line 9 (for this example, delete the row). Then, the Case Else code on line 10 just says "otherwise, don't touch line 9 & do what I tell you on row 11 (in this case move to the next row of the spreadsheet).

In your 2nd example, ALL_CODES is just a plain ol' ordinary string like I'm used to. No weird variant or array or anything that I should know about by now but don't. Its just a single string that contains all the branch codes I need to eliminate. Then BR_CODE is a normal string too. Then lines 9-13 are a good ol' fashioned if statement (again like what I'm used to) but line 9 has the piece thats new to me. InStr(1, ALL_CODES, BR_CODE) I'm guessing that counts how many occurences of the BR_CODE string exist within the ALL_CODES string and returns it as an integer...then if that integer is greater than 0 it deletes the row & if its not then it leaves that row and checks the next one.

Am I understanding both of those correctly? I feel like if I really understand the "why" instead of just the "how" I'll find myself becoming a much better programmer. Sorry for all the dumb questions in the meantime.

[si_the_geek]

Very close.. the only part you got wrong was InStr - it returns the position of one string (BR_CODE) within another (ALL_CODES), which will be 0 if it is not found. For more info, click on InStr in your code window, and press F1.


Don't worry about asking "dumb" questions.. it is much better to learn how things work, rather than simply copy & paste, as it means you will be able to solve similar issues by yourself later. It makes us feel better about helping too, as you are actually learning, rather than getting us to do your work.

To paraphrase what I've seen in a couple of members signatures: "It is better to appear dumb for a moment, than to remain dumb forever".