Basic Macro

infuse

Board Regular
Joined
Dec 7, 2005
Messages
126
Hello
I am a beginner in VBA world. Well, you can tell this by looking at my code.
I highly appreciate any help regarding this. Thanks in advance for the help.
I am trying to run the code below and it gives an error (This is the error “Loop without Do”)
Sub FirstProgram()
Dim A As Date
Dim B As Integer
Dim i As Integer
Do While i = 5
A = Ai
B = Weekday(A)
If 1 < B < 7 Then
Ji = Fi
i = i + 1
Loop Until i = 428
End Sub
I wanted to check the Value of “A” column (A contains dates) and if it is a weekday I wanted to copy the contents of F column to J column. I also want one more check before copying the Value from F to J column. I think I will do this later. Can anyone tell me why the above code is not working? Please don’t write the code for me. Please let me know what I am missing. Also keep in mind that I am new to this and this is my first program. This might be simple using the Excel functions and commands, but I wanted do this by writing a code.
Thanks in Advance
Infuse
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Welcome to the board.

Well, the construction of your Do loop is incorrect. Check out the Help topic on Loops in the VBA help topics.
Do...Loop Statement Example
This example shows how Do...Loop statements can be used. The inner Do...Loop statement loops 10 times, sets the value of the flag to False, and exits prematurely using the Exit Do statement. The outer loop exits immediately upon checking the value of the flag.

Dim Check, Counter
Check = True: Counter = 0 ' Initialize variables.
Do ' Outer loop.
Do While Counter < 20 ' Inner loop.
Counter = Counter + 1 ' Increment Counter.
If Counter = 10 Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.

Also, You ned an End If to go with the If.

Also, To set the value of a variable cell reference, you need something like Range("J" & i)=.....
 

infuse

Board Regular
Joined
Dec 7, 2005
Messages
126
Thanks a lot for the quick reply. I made some modification to my code. can comment on this please. Off course this is still not working. Thanks once again for the help.

Sub FirstPrograml()

Dim A As Date
Dim B As Integer
Dim i As Integer
For i = 5 To i = 428

A = Range("A" & i)
B = Weekday(A)
If 1 < B < 7 Then
Range("J" & i) = Range("F" & i)
End If
Next i

End Sub
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Sub FirstPrograml()

Dim A As Date
Dim B As Integer
Dim i As Integer

'Only check rows 5 to 428!
For i = 5 To 428

'Get row date!
A = ActiveSheet.Range("A" & i).Value
'Get weekday number!
B = Weekday(A)

'Test if weekday is not "1 or 7" and that this row does have a date!
If (B > 1 And B < 7) Then _
ActiveSheet.Range("J" & i).Value = ActiveSheet.Range("F" & i).Value
'Above: If true take column "F's" value and put it in column "J"
Next i

End Sub
 

infuse

Board Regular
Joined
Dec 7, 2005
Messages
126

ADVERTISEMENT

Thanks a lot for the help. I ran the code and I cannot see anything on my workboo (No out put). VB editor is not showing any error. If there is an error in the below code please point out and I will find out what it is and try to correct it.
Sub FirstProgram()
Dim A As Date
Dim B As Integer
Dim i As Integer
For i = 5 To i = 428
A = ActiveSheet.Range("A" & i).Value
B = Weekday(A)
If B > 1 And B < 7 Then
ActiveSheet.Range("J" & i).Value = ActiveSheet.Range("F" & i).Value
End If
Next i
End Sub
I inserted a module and wrote this code. There is only one active sheet (in fact there is only one sheet in this workbook). The name of the sheet is different (it is not sheet1).
I am giving all these information, incase if this helps to answer my question. I think the code is correct but something else is wrong here.
Thanks for all who tried to help me
Infuse
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

Your code has syntax errors!

See my revised code above!
 

infuse

Board Regular
Joined
Dec 7, 2005
Messages
126
Wow, that worked like a charm. I am going to add more code to this. I have to check one more thing before i copy the value from "F" to "J". My First program worked, Yahooooooooooooooo. I am happy now.
Thanks a lot for all who tried to help
 

infuse

Board Regular
Joined
Dec 7, 2005
Messages
126
Thanks once again for the help. I added another set of code to the existing one. Please comment on the code. it is not working. Please dont write the code for me. Let me know what is wrong. Thanks in advance
Infuse
B = Weekday(A)
If B > 1 And B < 7 Then

ActiveSheet.Range("J" & i).Value = ActiveSheet.Range("F" & i).Value
End If
If ActiveSheet.Range("C" & i) < Time(8, 0, 0) _
And ActiveSheet.Range("C" & i) > Time(21, 0, 0) Then
ActiveSheet.Range("J" & i).Value = 0
End If
Next i

End Sub
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
In your second If section you are basically looking to see if a time is both before 8:00 AM and after 9:00 PM. This will never be true. Maybe consider an OR instead?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,488
Members
412,669
Latest member
nickyon1
Top