Select or Loop?

[jrobin5881]

Being a relative novice with VBA I cannot figure out whether to Loop or use a select statement. I have in column C a somewhat stable series of elements. Example "C1"
Sale "C2" Del "C3" Tax and then it repeats down the column Sale, Del, Tax etc. The code attached does pretty much what I want (knowing that I have to tweak it a little more). But here's my problem. I only want the data calcualation to ocur in the active cell when "Sale" is seen in the "C" column. The offset works okay but in some of my data sets the row offset needs to change from 4 to 3 because tax dropped out of the series of elements.

My question is should I be using a case select statement to better process my list or am I not using the looping structure correctly? (sample code below, first part formats the sheet as you can see)

ub delColumns()

totDays = InputBox("Enter Total Working Days for Month")

'Range(Selection, Selection.End(xlDown)).Select
Range("D1:H1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("F1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlToLeft
Range("F2").Select
Loop2
Range("G2").Select
Loop2

End Sub


Sub Loop2()
' This loop runs as long as there is something in the next column

Do While IsEmpty(ActiveCell.Offset(0, -3)) = False
ActiveCell.Value = ActiveCell.Offset(0, -2).Value * totDays
ActiveCell.Offset(0, -2).Value = ActiveCell.Value
ActiveCell.Offset(4, 0).Select

Loop


End Sub

 

[firefytr]

Can you explain exactly what it is you are doing? Let me see if I understand this...

You want to go through each value in column C.
Where those values equal "Sale" perform a calculation.
That calculation is: same row, col A value * user input
Calculated value goes into xxxxxx?

Is that right?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[jrobin5881]

Column C is all text entries. The other columns have numbers in them that represent one days worth of data. So my input box has the user put in the total work days. I want to look down column C and find the rows that have Sales. When it finds the row the code should then take the data and multiply it times the number of days input by the end user. I'm going to add code to write the result back over the one days data. The problem is that sometimes Sales does not always fall uniformly 4 rows down from the row that the active cell cell is in so at this point I'm reluctant to overwrite the one days worth of data with the newly calculated result.

 

[firefytr]

Please explain this...

When it finds the row the code should then take the data..

Take what data? In what column? Take that data and multiply it by the number the user has input in an inputbox and put that data where? In the same cell/column where the number was found?

Also, what row are your headers on? Row 1 only? What row does your data start on? Just explain these basic logic steps and we'll get you code. You may not need a loop.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[jrobin5881]

Zack,

My apologies:
Yes, Row 1 has the column Headings only
and Yes replace the value in the same cell/column that was multiplied by my end user input value

Col "C" can be considered my conditional column. I want to start at the top of column C and go down the column and each time you find the text "Sale" I want my code to look at the number in column "D" (in the same row) and multiply that value by the number my end user puts in my input box. I want to over write the number in column "D" with the result of that multiplication. I want to repeat the same step for column "E" So that for each instance of "Sale" in column "C" the code will multiply the data in column "D" and "E" by the inputbox value and replace each column with the respective results of the formula.

 

[firefytr]

Dim totDays as variant, c as range, ws as worksheet
totDays = inputbox("Enter Total Working Days for Month:")
if totDays = "" or not typename(totDays) = "Long" then
    msgbox "Please, whole numbers only!"
    exit sub
end if
Set ws = sheets("Sheet1") '## Set sheet name here
For each c in ws.Range("C2", ws.Cells(ws.rows.count, 3).end(xlup))
    If c.Value = "Sale" Then
        ws.Cells(c.row, "D").value = ws.Cells(c.row, "D").value * totDays
        ws.Cells(c.row, "E").value = ws.Cells(c.row, "E").value * totDays
    End if
Next c

This assumes there is numbers in cols D & E. This is currently untested as my apps are acting up. If need be I'll copy on another machine. Change the sheet name to whatever sheet this is on.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[jrobin5881]

I knew I had to be over complicating this -Thanks you really got me back on track. At this point I can read code and understand what it's doing, it's the knowing which works best under which conditions that I struggle with.

I had to comment out the inputbox to test. It kept looping giving the error message even when a whole number was entered.

totDays = InputBox("Enter Total Working Days for Month:")

'If totDays = "" Or Not TypeName(totDays) = "Long" Then
' MsgBox "Please, whole numbers only!"
' Exit Sub
'End If

 

[firefytr]

It may have been defaulting to a Double instead of a Long. That was an assumption on my part. You could always just check for IsNumeric() if you wanted to. If you wanted to make sure it was a whole number, check for Int(val) = val.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci