Check a cell's value and populate other cells with varying data

[Demosthenes&Locke]

I have a spreadsheet with a column that has text, I need it to check to see if another column in the same row has text in it, then fill other cells in the same row but different columns accordingly.

Basically a while loop with a case statement for each check, and the while loop tests if the cell has a value in it before processing, when it reaches the end of the data it stops, there will never be a blank cell before the end of the column.

nRow = 3
nCol = 3
 While (Cells(nRow,nCol))
 If ActiveSheet.Cells(nRow,(nCol-1)).Value = "Box"
   ActiveSheet.Cells(nRow,(nCol+1)).Value = "Virtual"
   ActiveSheet.Cells(nRow,(nCol+2)).Value = "Place1"
   ActiveSheet.Cells(nRow,(nCol+3)).Value = "Place2"
   ActiveSheet.Cells(nRow,(nCol-1)).Value = Range("B2") & " " & ActiveSheet.Cells(nRow,nCol)
 End if
 nRow=nRow+1
wEnd

B2 will be a number(text?) that will change with each sheet, but each time I run the macro it will be using the number from that cell on that sheet, and it's giving me problems.
I know this code isn't exact, but I was doing it at work and I forgot to bring it with me, so this is a basic sketch of what I am working with.

..........A..........B.........C.........D........E.........F..........G
1
2...................90-91
3....................Box.....System
4...................Junct....Transf
5....................Box.....System

To become

............A............B.........C.........D.............E.........F..........G
1
2.....................90-91
3..90-91.Box.....Box...System....Virtual....Place1.....Place2......
4.....................Junct...Transf
5..90-91.Box.....Box...System....Virtual....Place1.....Place2......

It will always start at the same row+column, but it needs to go until it runs out of stuff in the column

[davesexcel]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Demosthenes&Locke]

I ended up fixing it on my own.

[Demosthenes&Locke]

Solution:

Option Compare Text

Sub FillOutFast()

i = 0 'column offset
j = 18

'MsgBox "The value of the cell is: " & Worksheets(Task List).Range("A2").Offset(j, i).Value
'MsgBox "The value of the cell is: " & Worksheets(Task List).Range("A2").Offset(j, i + 1).Value
'MsgBox "The value of the cell is: " & Worksheets(Task List).Range("A2").Offset(j, i + 2).Value
'MsgBox "The value of the cell is: " & Worksheets(Task List).Range("A2").Offset(j, i + 3).Value
'MsgBox "The value of the cell is: " & Worksheets(Task List).Range("A2").Offset(j, i + 4).Value

'Whichever cell/column/row you use for your offset should be a standard size, if it's larger or merged,
'the offset will affect which cell it considers offset +1, offset +2, etc.


For j = 18 To 99 ' rows
If Not Worksheets(Task List).Range("A2").Offset(j, i + 2).Value = "" Then

If Worksheets(Task List).Range("A2").Offset(j, i + 2).Value = "Easter egg hunt" Then
    Worksheets(Task List).Range("A2").Offset(j, i + 7).Value = "Prepare Eggs"
    Worksheets(Task List).Range("A2").Offset(j, i + 8).Value = "Prepare Food"
    Worksheets(Task List).Range("A2").Offset(j, i + 9).Value = "Clean up"
End If
'add as many if statements here as you want.
End If
Next j
End Sub

I would have liked to have done it with a while statement, and used the method of activesheet.cells to control the cells so I could have used a variable without having to use an offset, and I would have preferred a case statement, so if anyone can write one to do it that way and verify it is working, please post it here for the archive.