help with Iterative loop 2

[Excelerate2004]

Hello to all,

I have a range of cells from B2:B536 (Vertically).

There is a text value in B2 and 2 empty cells below that, text value in B5 and 2 empty cells below that,
text value in B8, 2 empty cells etc...all the way to B536

I want to be able to look in B2, B5, B8 etc. and based on what value appears in each, use IF statements to place
values in the 2 empty cells all the way to B536, this is my logic as follows:

If Sheets("Sheet3").Range("B2") = "G" Then
    Sheets("Sheet3").Range("B3") = "G"
    Sheets("Sheet3").Range("B4") = "G"
End If

If Sheets("Sheet3").Range("B2") = "GA" Then
    Sheets("Sheet3").Range("B3") = "G"
    Sheets("Sheet3").Range("B4") = "A"
End If

If Sheets("Sheet3").Range("B2") = "A" Then
    Sheets("Sheet3").Range("B3") = "A"
    Sheets("Sheet3").Range("B4") = "A"
End If

If Sheets("Sheet3").Range("B2") = " " Then
    Sheets("Sheet3").Range("B3") = "?"
    Sheets("Sheet3").Range("B4") = "?"
End If

This is what Ive attempted below, how close am I to a solution?

Or is there a better way? Does this code even work?

Dim row As Integer
  row = 2

Do

    'If (IsEmpty(Sheet3.Cells(row + 1, 1).Value)) Then Exit 

    If Sheet3.Cells("row + 2, 2") = "G" Then
        Sheet3.Cells("row + 3, 2") = "G"
        Sheet3.Cells("row + 4, 2") = "G"
    End If

    If Sheet3.Cells("row + 2, 2") = "GA" Then
        Sheet3.Cells("row + 3, 2") = "G"
        Sheet3.Cells("row + 4, 2") = "A"
    End If

    If Sheet3.Cells("row + 2, 2") = "A" Then
        Sheet3.Cells("row + 3, 2") = "A"
        Sheet3.Cells("row + 4, 2") = "A"
    End If

    If Sheet3.Cells("row + 2, 2") = " " Then
        Sheet3.Cells("row + 3, 2") = "?"
        Sheet3.Cells("row + 4, 2") = "?"
    End If

    row = row + 3

Loop

Thanks for any help

 

[JerryKlmns]

Excelerate2004,

Try this

For Row = 2 To 536 Step 3
Select Case Sheet3.Cells(row, 2).Value
Case Is "G"
Sheet3.Cells(row + 1, 2).Value = "G"
Sheet3.Cells(row + 2, 2).Value = "G"
Case Is "GA"
Sheet3.Cells(row + 1, 2).Value = "G"
Sheet3.Cells(row + 2, 2).Value = "A"
Case Is "A"
Sheet3.Cells(row + 1, 2).Value = "A"
Sheet3.Cells(row + 2, 2).Value = "A"
Case Else
Sheet3.Cells(row + 1, 2).Value = "?"
Sheet3.Cells(row + 2, 2).Value = "?"
End Select
Next Row

 

[SkipVought]

Hi,

1. the calculation for the last row...

With Sheet3
   lRowLast = .Cells(.Cells.Rows.Count, "B").End(xlUP).Row
End With

then just use a for...next

With Sheet3
   lRowLast = .Cells(.Cells.Rows.Count, "B").End(xlUP).Row
   for lRow = 2 to iRowLast Step 3
      with .Cells(lRow, "B")
         .offset(1,0).value = .value
         .offset(2,0).value = .value
      end with
   next
End With

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[HarleyQuinn]

Something like:

Dim i as Integer
Dim Value as String

for i = 2 to 568 step 3
value = sheet3.cells(i,"2")
sheet3.cells(i+1,"2") = left(value,1)
sheet3.cells(i+2,"2") = right(value,1)
next i

I just knocked that up on the spot and it is untested so may require some tweaking but it should give you the idea.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[HarleyQuinn]

I'm just too slow nowadays

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[SkipVought]

i guess I missed something

With Sheet3
   lRowLast = .Cells(.Cells.Rows.Count, "B").End(xlUP).Row
   for lRow = 2 to iRowLast Step 3
      with .Cells(lRow, "B")
         select case .value
           case "G", "A"
             .offset(1,0).value = .value
             .offset(2,0).value = .value
           case "GA"
             .offset(1,0).value = left(.value,1)
             .offset(2,0).value = right(.value,1)
           case " "
             .offset(1,0).value = "?"
             .offset(2,0).value = "?"
         end select
      end with
   next
End Wit

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[Excelerate2004]

Just going thru the different code posts from top to bottom:

jerryklmns I've tried your code and tested it, it works fine! Thanks.

I'll start to go through the others just to see the different solutions.

I'm having trouble grasping the concept of rows and looping.

I find my problem is where I'm not constantly pogramming I forget what I learn in previous examples and I feel its like having to start over each time.

I'm not sure if this would be better in a separate post but is there a definitive book or resource out there that I could get at my local book shop on VBA scripting??


Thanks for all of your help.

 

[HarleyQuinn]

I made the same mistake as Skip and forgot the "?". Oops!
My code should read:

Dim i as Integer
Dim Value as String

for i = 2 to 568 step 3
value = sheet3.cells(i,"2")
if value = " " then value = "?"
sheet3.cells(i+1,"2") = left(value,1)
sheet3.cells(i+2,"2") = right(value,1)
next i

Just thought I would update it as you said you would be trying the other solutions as well.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[HarleyQuinn]

And having just tested mine you would also need to remove the "'s in the .Cell() for it to work correctly.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[Excelerate2004]

Hi Harleyquinn, (nice name btw),

I just tested your code and it works minus the "'s and this line:

if value = " " then value = "?"

Should be:

if value = "" then value = "?"

But thats probably more my fault than anything.

I haven't exactly figured out how your code works yet, but I will.

I'd like to eventually cover an entire range from B2:I536 with additional cases so that I could cover it all off instead of doing it column by column. Is that possible?

Thanks!

 

[HarleyQuinn]

Hi Excelerate2004,

I will give you the basic outline of my code:

Dim i as Integer
Dim Value as String

The basic declarations

for i = 2 to 568 step 3

This is the start of the for loop. it will currently start at 2 and then go to 568. it won't go through every number between it will skip 2 numbers each time using the step 3 i.e it will increment (step) from 2 to 5 and then 5 to 8 etc.

value = sheet3.cells(i,"2")

this sets the string variable to the value of the cell you want to look at, in the case of the first time it loop the cell will be 2,2.

if value = "" then value = "?"

if the value of that cell is blank then set the string value equal to "?"

sheet3.cells(i+1,"2") = left(value,1)
sheet3.cells(i+2,"2") = right(value,1)

i quite liked this bit of code i must admit. If the string is two characters long e.g. GA then it will put the left character in the next cell down and the right in the cell after. if it is only one character e.g. G thenthe left and right functions return the same value and populate the cells in the same way.

next i

to the start of the loop again.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[Excelerate2004]

Hi Skip,

I tried your code sample, but couldnt get it working. I created a command button, but couldnt get it to give me back any errors? Not sure whats going on there?

Cheers,

E.

 

[Excelerate2004]

Hi again HQ,

I thought this bit of code was quite neat as well;

sheet3.cells(i+1,"2") = left(value,1)
sheet3.cells(i+2,"2") = right(value,1)

It saves from having to use IF statements or CASES.

I'm going to try and adapt it so that I can run this same script over an entire range and not just 1 column, because again that would mean I wouldnt have to set up a bunch of IF's for different combinations of letters in my range.

Thanks again!!

 

[Excelerate2004]

Hey again,

I slightly modified HQ's code just so that it could loop through my entire worksheet instead of 1 column. I definitely learned something today!

Dim i As Integer
    Dim j As Integer
    
    Dim Value As String

    For i = 2 To 536 Step 3
        For j = 2 To 9
            Value = Sheet3.Cells(i, j)
            If Value = "" Then Value = "?"
            Sheet3.Cells(i + 1, j) = Left(Value, 1)
            Sheet3.Cells(i + 2, j) = Right(Value, 1)
        Next j
    Next i

Thanks again to all!

Cheers