VBA Looping Question

[JimFor]

Hi,

I'm writing a program to manipulate some data on a spreadsheet and keep getting
errors in the looping section. The program keeps telling me it can't find part
of the the, for lack of a better term, looping instruction. Errors indicate I
have "something" without "something else." No matter what kind of looping
code I wrote, I keep getting the same kind of error.

Here is an example of what I am trying to do. I am comparing the values of
two cells in the same row. If the value of the cell in the second column of
the row equels zero, put the value found in the cell in the first column into
the cell found in the second column If the value of the cell in the second
column is not a zero, leave it along and go down one row and repeat the
procedure untill all the cells are examined Like I said, I have tried several
approaches. Here is an example of my simple For...Next loop test program which
looks at only four cells. I'll expand it once I get it to work. Can tell me
what I am doing wrong? I have values in Cells (1,1) through Cells (2,2) I
have a zero in Cell (2,1). When I run this program, I get a "Next without For"
error message.

Public Sub Same()
'Create variables to hold two numbers
Dim Input1 As Double
Dim Input2 As Double
'Create Count Variable
Dim Count As Double
'Set Count
For Count = 1 To 2
<======== But I do have a "For" in the program.
'Check cell valules
Input1 = Sheet1.Cells(1, Count)
Input2 = Sheet1.Cells(2, Count)
'Compare/change cell contents
If Input2 = 0 Then
Input2 = Input1
Else
Input2 = Input2
Next Count
End Sub

But obviously I am doing something wrong. And it applies to many different
kinds of loops. Once I understand this it will help me in writing future
programs.

Thanks

 

[JE McGimpsey]

You're missing an "End If". If you indent your code, it's easier to see:

Public Sub Same()
'Create variables to hold two numbers
Dim Input1 As Double
Dim Input2 As Double
'Create Count Variable
Dim Count As Double
'Set Count
For Count = 1 To 2
'Check cell valules
Input1 = Sheet1.Cells(1, Count)
Input2 = Sheet1.Cells(2, Count)
'Compare/change cell contents
If Input2 = 0 Then
Input2 = Input1
Else
Input2 = Input2
Next Count
End Sub


The End Is statement has to go after the statements in the Else block
(e.g., just before the Next Count statement, above)

 

[JimFor]

Thanks. I don't get an error message. (Yeah!) But the value in Cell (2,1)
remains zero. So what have I done wrong? What do I have to do to change it to
the value in Cell (1,1)? I thought once there were no bugs the zero value
wuuld automatically change. I might add that any none zero value in a cell is
in a date format. Any cell which does not have a date in it has a zero in it.
(But even if I put all numbers in the call and use a General format, the zero
does not change. )

Thanks.

 

[JE McGimpsey]

You never assign the value to a cell. This may do what you want:

If Input2 = 0 Then
Sheet1.Cells(2, Count) = Input1
Else
Sheet1.Cells(2, Count) = Input2
End If

But if that's what the goal of the macro is, I'd change it to something
like

Public Sub Same2()
Dim nCount As Long
For nCount = 1 To 2
With Sheet1.Cells(2, nCount)
If .Value = 0 Then .Value = .Offset(-1, 0).Value
End With
End With
End Sub

and avoid the intermediate variables altogether.

In general it's better not to name variables with VBA reserved words,
which is why I replaced Count with nCount.

 

[JimFor]

OK. I think I know what you are doing. However, when I run what you wrote , I
get another ''without " error. This time it is "End With" without "With."
Since I keep getting these types of errors, besides what is wrong now, can you
tell me what is the error in programming logic which causes that?
Thanks

 

[Dave Peterson]

J.E. had a typo:


Public Sub Same2()
Dim nCount As Long
For nCount = 1 To 2
With Sheet1.Cells(2, nCount)
If .Value = 0 Then .Value = .Offset(-1, 0).Value
End With
Next nCount '<---
End Sub

(Here's hoping that I didn't introduce another typo!)

 

[JE McGimpsey]

Thanks for the correction!

 

[JimFor]

Thanks, but neither version changes anything. I even tried an "If" statement
which would change one cell value to a zero if the two cells had the same value
and made two cell the same value. No zero appeared. Still stumped.
This is what I have set up.

COL A COL B
Row 1 1/25/00 0
Row 2 2/25/00 2/2/01

I am trying to have the program recogize that there is a zero in Row 1/Col B
and replace it with "1/25/00." This should not be all that difficult to do but
I am missing something.

 

[JimFor]

I think the problem lies with how one references the cells. Just tried
changing the value in Col B to the value in Col A without a loop and it worked
when I placed a zero in the Col B cell. I'm not too good yet at referencing
Excel cells I guess.

 

[JimFor]

This seems to work.

Public Sub Same()
'Create variables to hold two numbers
Dim Input1 As Double
Dim Input2 As Double
'Create Count Variable
Dim Count As Double
'Set Count
For Count = 1 To 2
'Check cell valules
Input1 = Sheet1.Cells(Count, 1)
Input2 = Sheet1.Cells(Count, 2)
'Compare/change cell contents
If Input2 = 0 Then
Sheet1.Cells(Count, 2) = Input1
Else
Sheet1.Cells(Count, 1) = Input2
End If

Next Count
End Sub

Thanks for the replies. Got me thinking correctly.