Can somebody help me with this code?

B

Brett Smith

I am programming a macro based on some information on the spreadsheet. As it
goes through, if it is missing certain information it will choose one format,
and if it has certain information which is in column C, it will choose
another format. Afterwards, it will take the information, spit it out into a
text file saved in RDF format. The problem is, I ran it and I got a "Compile
Error, For needed for Next". But the thing is, I have a For statement
towards the top. When I put a For there, it starts asking for Ifs to close
End Ifs or End Ifs to close Ifs. I'm confused but I thought my program would
have worked. Code is below.



Sub GetRows()
Dim FirstCell As Range, LastCell As Range
On Error Resume Next
Dim Firstrow As Long, Lastrow As Long
Dim Wordstring As String
Dim filePath As String
Dim I As Integer
Dim FName As Variant
Dim Rangecount As Integer
Dim intresult As Long
Dim NVariable As String
Dim MVariable As String
Dim AVariable As String
Dim AVARSTRING As String
Dim FVariable As String
Dim FVARSTRING As String
Dim EVariable As String
Dim EVARSTRING As String
Dim NandMVariable As String



Kill (filePath = ActiveWorkbook.Path & "\Seqfile.rdf")

I = 0
Tried = False
Tried2 = False



Wordstring = "$RDFILE 1" & vbCrLf & _
"$DATM " & Date & " " & Time & vbCrLf & _
"$RIREG 1" & vbCrLf & _
"$DTYPE BATCH:CHEMIST" & vbCrLf & _
"$DATUM REIDHAAJ" & vbCrLf & _
"$DTYPE BATCH:STRUCT_CMNT" & vbCrLf & _
"$DATUM [NUCLEIC ACID]" & vbCrLf & _
"$DTYPE STRUCTURE" & vbCrLf & _
"$DATUM $MFMT"

filePath = ActiveWorkbook.Path & "\Seqfile.rdf"
Open filePath For Output As #1
Print #1, Wordstring
Do
GetCell:
On Error Resume Next
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only ", Type:=8)

On Error GoTo 0
If FirstCell Is Nothing Then
MsgBox "You pressed Cancel!" & IIf(Tried, "AGAIN! Good-bye!", "!")
If Tried Then Exit Sub
Tried = True
GoTo GetCell
Else
MsgBox FirstCell.Address
End If
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row


Do
GetCell2:
On Error Resume Next
Set LastCell = Application.InputBox("Enter bottom right cell - ONE
cellonly ", Type:=8)
On Error GoTo 0
If LastCell Is Nothing Then
MsgBox "You pressed Cancel!" & IIf(Tried, "AGAIN! Good-Bye!", "!")
If Tried2 Then Exit Sub
Tried2 = True
GoTo GetCell2
Else
MsgBox LastCell.Address
End If
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row


Firstrow = Int(Firstrow)
Lastrow = Int(Lastrow)


MsgBox Firstrow & " - " & Lastrow

Rangecount = Lastrow - Firstrow

MsgBox Rangecount & " records exported"
Range(Firstrow & ":" & Lastrow).Select

For I = Firstrow To Lastrow
If IsEmpty(Cells(I, "C").Value) Then

Dim G As Variant
Dim H As Variant

'Select Case IsEmpty(Cells(I, "G").Value) Or IsNull(Cells(I,
"G").Value) 'Cells(I, "G").Value = " "
If IsEmpty(Cells(I, "N").Value) Then
NVariable = Null
Else: NVariable = Cells(I, "N").Value
End If

If IsEmpty(Cells(I, "M").Value) Then
MVariable = Null
Else: MVariable = ""
End If

NandMVariable = NVariable + MVariable


If IsEmpty(NandMVariable) Then
NandMVariable = ""
Else
If IsEmpty(NVariable) Then
NandMVariable = "char(59) & ' ' & char(34) & $DATUM &
char(34) & MVariable & char(59)"
Else
If IsEmpty(MVariable) Then
NandMVariable = "char(59) & ' ' & char(34) & $DATUM &
char(34) & NVariable & char(59)"
Else
NandMVariable = " char(34) & $DATUM & char(34) & NVariable &
char(34)_char(34) & MVariable(char(59)"
End If

If IsEmpty(Cells(I, "A").Value) Then
AVariable = Null
Else: AVariable = Cells(I, "A").Value
End If

If IsEmpty(AVariable) Then
AVARSTRING = ""
Else
AVARSTRING = "char(34) & $DATUM siRNA for Gene target:char(34) &
char(38) & Avariable & char(38) & char(34) & char(59) & char(34) & char(44)"
End If

If IsEmpty(Cells(I, "F").Value) Then
FVariable = Null
Else: FVariable = Cells(I, "F").Value
End If

If IsEmpty(FVariable) Then
FVARSTRING = ""
Else
FVARSTRING = FVariable
End If


If IsEmpty(Cells(I, "E").Value) Then
EVariable = Null
Else: EVariable = Cells(I, "E").Value
End If

If IsEmpty(EVariable) Then
EVARSTRING = ""
Else: EVARSTRING = EVariable
End If



Print #1, vbCrLf; " -ISIS- 10310514382D" & vbCrLf & vbCrLf _
; " 0 0 0 0 0 0 0 0 0 0999 v2000" & vbCrLf _
; "M END" & vbCrLf _
; "$DTYPE BATCH:LAB_JOURNAL" & vbCrLf _
; NandMVariable & vbCrLf _
; "$DTYPE BATCH:LIN_STRUCT_CODE" & vbCrLf _
; "$DATUM N" & vbCrLf _
; "$DTYPE BATCH:LIN_STRUCT_DESC" & vbCrLf _
; "$DATUM Pool components: Pool1-1; Pool1-2; Pool1-3" &
vbCrLf _
; "$DTYPE BATCH:pRODUCER(1):pRODUCER" & vbCrLf _
; "$DATUM " & Cells(I, "R").Value & ";", vbCrLf _
; "$DTYPE BATCH:pREP_DESCR" & vbCrLf _
; AVARSTRING & vbCrLf _
; FVARSTRING & vbCrLf _
; EVARSTRING & vbCrLf _
; "$DTYPE BATCH:GENERIC_NAME(1):GENERIC_NAME" & vbCrLf _
; "$DATUM " & Cells(I, "B").Value & ";"; vbCrLf _
; "$RIREG " & I - 2 & vbCrLf _
; "$DTYPE BATCH:CHEMIST" & vbCrLf _
; "$DATUM REIDHAAJ" & vbCrLf _
; "$DTYPE BATCH:STRUCT_CMNT" & vbCrLf _
; "$DATUM [NUCLEIC ACID]" & vbCrLf _
; "$DTYPE STRUCTURE" & vbCrLf _
; "$DATUM $MFMT"





'End Select


' Else

'Select Case IsEmpty(Cells(I, "H").Value) Or IsNull(Cells(I,
"H").Value) 'Cells(I, "H").Value = " "


If IsEmpty(Cells(I, "N").Value) Then
NVariable = Null
Else: NVariable = Cells(I, "N").Value
End If

If IsEmpty(Cells(I, "M").Value) Then
MVariable = Null
Else: MVariable = ""
End If

NandMVariable = NVariable + MVariable


If IsEmpty(NandMVariable) Then
NandMVariable = ""
Else
If IsEmpty(NVariable) Then
NandMVariable = "char(59) & ' ' & char(34) & $DATUM &
char(34) & MVariable & char(59)"
Else
If IsEmpty(MVariable) Then
NandMVariable = "char(59) & ' ' & char(34) & $DATUM &
char(34) & NVariable & char(59)"
Else
NandMVariable = " char(34) & $DATUM & char(34) & NVariable &
char(34)_char(34) & MVariable(char(59)"
End If


If IsEmpty(Cells(I, "A").Value) Then
AVariable = Null
Else: AVariable = Cells(I, "A").Value
End If

If IsEmpty(AVariable) Then
AVARSTRING = ""
Else
AVARSTRING = "char(34) & $DATUM siRNA for Gene target:char(34) &
char(38) & Avariable & char(38) & char(34) & char(59) & char(34) & char(44)"
End If

If IsEmpty(Cells(I, "F").Value) Then
FVariable = Null
Else: FVariable = Cells(I, "F").Value
End If

If IsEmpty(FVariable) Then
FVARSTRING = ""
Else
FVARSTRING = FVariable
End If

If IsEmpty(Cells(I, "E").Value) Then
EVariable = Null
Else: EVariable = Cells(I, "E").Value
End If

If IsEmpty(EVariable) Then
EVARSTRING = ""
Else: EVARSTRING = EVariable
End If





Print #1, vbCrLf; " -ISIS- 10310514382D" & vbCrLf & vbCrLf _
; " 0 0 0 0 0 0 0 0 0 0999 v2000" & vbCrLf _
; "M END" & vbCrLf _
; "$DTYPE BATCH:LAB_JOURNAL" & vbCrLf _
; NandMVariable & vbCrLf _
; "$DTYPE BATCH:LIN_STRUCT_CODE" & vbCrLf _
; "$DATUM N" & vbCrLf _
; "$DTYPE BATCH:LIN_STRUCT_DESC" & vbCrLf _
; "$DATUM Sense Strand: " & Cells(I, "C").Value; ";" & "
Antisense Strand:" & vbCrLf _
; Cells(I, "D").Value & ";", vbCrLf _
; "$DTYPE BATCH:pRODUCER(1):pRODUCER" & vbCrLf _
; "$DATUM " & Cells(I, "R").Value & ";", vbCrLf _
; "$DTYPE BATCH:pREP_DESCR" & vbCrLf _
; AVARSTRING & vbCrLf _
; FVARSTRING & vbCrLf _
; EVARSTRING & vbCrLf _
; "$DTYPE BATCH:GENERIC_NAME(1):GENERIC_NAME" & vbCrLf _
; "$DATUM " & Cells(I, "B").Value & ";", vbCrLf _
; "$RIREG " & I - 2 & vbCrLf _
; "$DTYPE BATCH:CHEMIST" & vbCrLf _
; "$DATUM REIDHAAJ" & vbCrLf _
; "$DTYPE BATCH:STRUCT_CMNT" & vbCrLf _
; "$DATUM [NUCLEIC ACID]" & vbCrLf _
; "$DTYPE STRUCTURE" & vbCrLf _
; "$DATUM $MFMT"









End If

Next I
Close #1


End Sub
 
B

ben

VB reads If End If blocks by levels same with For...Next and Do...Loop
So odds are you have not closed an If...End If Block of Do...Loop correctly,
you might want to count your If Statements and End If and make sure that you
don't start a FOR statement INSIDE an IF block but have the next statement
OUTSIDE that same IF block
Ben
--
When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?


Brett Smith said:
I am programming a macro based on some information on the spreadsheet. As it
goes through, if it is missing certain information it will choose one format,
and if it has certain information which is in column C, it will choose
another format. Afterwards, it will take the information, spit it out into a
text file saved in RDF format. The problem is, I ran it and I got a "Compile
Error, For needed for Next". But the thing is, I have a For statement
towards the top. When I put a For there, it starts asking for Ifs to close
End Ifs or End Ifs to close Ifs. I'm confused but I thought my program would
have worked. Code is below.



Sub GetRows()
Dim FirstCell As Range, LastCell As Range
On Error Resume Next
Dim Firstrow As Long, Lastrow As Long
Dim Wordstring As String
Dim filePath As String
Dim I As Integer
Dim FName As Variant
Dim Rangecount As Integer
Dim intresult As Long
Dim NVariable As String
Dim MVariable As String
Dim AVariable As String
Dim AVARSTRING As String
Dim FVariable As String
Dim FVARSTRING As String
Dim EVariable As String
Dim EVARSTRING As String
Dim NandMVariable As String



Kill (filePath = ActiveWorkbook.Path & "\Seqfile.rdf")

I = 0
Tried = False
Tried2 = False



Wordstring = "$RDFILE 1" & vbCrLf & _
"$DATM " & Date & " " & Time & vbCrLf & _
"$RIREG 1" & vbCrLf & _
"$DTYPE BATCH:CHEMIST" & vbCrLf & _
"$DATUM REIDHAAJ" & vbCrLf & _
"$DTYPE BATCH:STRUCT_CMNT" & vbCrLf & _
"$DATUM [NUCLEIC ACID]" & vbCrLf & _
"$DTYPE STRUCTURE" & vbCrLf & _
"$DATUM $MFMT"

filePath = ActiveWorkbook.Path & "\Seqfile.rdf"
Open filePath For Output As #1
Print #1, Wordstring
Do
GetCell:
On Error Resume Next
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only ", Type:=8)

On Error GoTo 0
If FirstCell Is Nothing Then
MsgBox "You pressed Cancel!" & IIf(Tried, "AGAIN! Good-bye!", "!")
If Tried Then Exit Sub
Tried = True
GoTo GetCell
Else
MsgBox FirstCell.Address
End If
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row


Do
GetCell2:
On Error Resume Next
Set LastCell = Application.InputBox("Enter bottom right cell - ONE
cellonly ", Type:=8)
On Error GoTo 0
If LastCell Is Nothing Then
MsgBox "You pressed Cancel!" & IIf(Tried, "AGAIN! Good-Bye!", "!")
If Tried2 Then Exit Sub
Tried2 = True
GoTo GetCell2
Else
MsgBox LastCell.Address
End If
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row


Firstrow = Int(Firstrow)
Lastrow = Int(Lastrow)


MsgBox Firstrow & " - " & Lastrow

Rangecount = Lastrow - Firstrow

MsgBox Rangecount & " records exported"
Range(Firstrow & ":" & Lastrow).Select

For I = Firstrow To Lastrow
If IsEmpty(Cells(I, "C").Value) Then

Dim G As Variant
Dim H As Variant

'Select Case IsEmpty(Cells(I, "G").Value) Or IsNull(Cells(I,
"G").Value) 'Cells(I, "G").Value = " "
If IsEmpty(Cells(I, "N").Value) Then
NVariable = Null
Else: NVariable = Cells(I, "N").Value
End If

If IsEmpty(Cells(I, "M").Value) Then
MVariable = Null
Else: MVariable = ""
End If

NandMVariable = NVariable + MVariable


If IsEmpty(NandMVariable) Then
NandMVariable = ""
Else
If IsEmpty(NVariable) Then
NandMVariable = "char(59) & ' ' & char(34) & $DATUM &
char(34) & MVariable & char(59)"
Else
If IsEmpty(MVariable) Then
NandMVariable = "char(59) & ' ' & char(34) & $DATUM &
char(34) & NVariable & char(59)"
Else
NandMVariable = " char(34) & $DATUM & char(34) & NVariable &
char(34)_char(34) & MVariable(char(59)"
End If

If IsEmpty(Cells(I, "A").Value) Then
AVariable = Null
Else: AVariable = Cells(I, "A").Value
End If

If IsEmpty(AVariable) Then
AVARSTRING = ""
Else
AVARSTRING = "char(34) & $DATUM siRNA for Gene target:char(34) &
char(38) & Avariable & char(38) & char(34) & char(59) & char(34) & char(44)"
End If

If IsEmpty(Cells(I, "F").Value) Then
FVariable = Null
Else: FVariable = Cells(I, "F").Value
End If

If IsEmpty(FVariable) Then
FVARSTRING = ""
Else
FVARSTRING = FVariable
End If


If IsEmpty(Cells(I, "E").Value) Then
EVariable = Null
Else: EVariable = Cells(I, "E").Value
End If

If IsEmpty(EVariable) Then
EVARSTRING = ""
Else: EVARSTRING = EVariable
End If



Print #1, vbCrLf; " -ISIS- 10310514382D" & vbCrLf & vbCrLf _
; " 0 0 0 0 0 0 0 0 0 0999 v2000" & vbCrLf _
; "M END" & vbCrLf _
; "$DTYPE BATCH:LAB_JOURNAL" & vbCrLf _
; NandMVariable & vbCrLf _
; "$DTYPE BATCH:LIN_STRUCT_CODE" & vbCrLf _
; "$DATUM N" & vbCrLf _
; "$DTYPE BATCH:LIN_STRUCT_DESC" & vbCrLf _
; "$DATUM Pool components: Pool1-1; Pool1-2; Pool1-3" &
vbCrLf _
; "$DTYPE BATCH:pRODUCER(1):pRODUCER" & vbCrLf _
; "$DATUM " & Cells(I, "R").Value & ";", vbCrLf _
; "$DTYPE BATCH:pREP_DESCR" & vbCrLf _
; AVARSTRING & vbCrLf _
; FVARSTRING & vbCrLf _
; EVARSTRING & vbCrLf _
; "$DTYPE BATCH:GENERIC_NAME(1):GENERIC_NAME" & vbCrLf _
; "$DATUM " & Cells(I, "B").Value & ";"; vbCrLf _
; "$RIREG " & I - 2 & vbCrLf _
; "$DTYPE BATCH:CHEMIST" & vbCrLf _
; "$DATUM REIDHAAJ" & vbCrLf _
; "$DTYPE BATCH:STRUCT_CMNT" & vbCrLf _
; "$DATUM [NUCLEIC ACID]" & vbCrLf _
; "$DTYPE STRUCTURE" & vbCrLf _
; "$DATUM $MFMT"





'End Select


' Else

'Select Case IsEmpty(Cells(I, "H").Value) Or IsNull(Cells(I,
"H").Value) 'Cells(I, "H").Value = " "


If IsEmpty(Cells(I, "N").Value) Then
NVariable = Null
Else: NVariable = Cells(I, "N").Value
End If

If IsEmpty(Cells(I, "M").Value) Then
MVariable = Null
Else: MVariable = ""
End If

NandMVariable = NVariable + MVariable


If IsEmpty(NandMVariable) Then
NandMVariable = ""
Else
If IsEmpty(NVariable) Then
NandMVariable = "char(59) & ' ' & char(34) & $DATUM &
char(34) & MVariable & char(59)"
Else
If IsEmpty(MVariable) Then
NandMVariable = "char(59) & ' ' & char(34) & $DATUM &
char(34) & NVariable & char(59)"
Else
NandMVariable = " char(34) & $DATUM & char(34) & NVariable &
char(34)_char(34) & MVariable(char(59)"
End If


If IsEmpty(Cells(I, "A").Value) Then
AVariable = Null
Else: AVariable = Cells(I, "A").Value
End If

If IsEmpty(AVariable) Then
AVARSTRING = ""
Else
AVARSTRING = "char(34) & $DATUM siRNA for Gene target:char(34) &
char(38) & Avariable & char(38) & char(34) & char(59) & char(34) & char(44)"
End If

If IsEmpty(Cells(I, "F").Value) Then
FVariable = Null
Else: FVariable = Cells(I, "F").Value
End If

If IsEmpty(FVariable) Then
FVARSTRING = ""
Else
FVARSTRING = FVariable
End If

If IsEmpty(Cells(I, "E").Value) Then
EVariable = Null
Else: EVariable = Cells(I, "E").Value
End If

If IsEmpty(EVariable) Then
EVARSTRING = ""
Else: EVARSTRING = EVariable
End If





Print #1, vbCrLf; " -ISIS- 10310514382D" & vbCrLf & vbCrLf _
; " 0 0 0 0 0 0 0 0 0 0999 v2000" & vbCrLf _
; "M END" & vbCrLf _
; "$DTYPE BATCH:LAB_JOURNAL" & vbCrLf _
; NandMVariable & vbCrLf _
; "$DTYPE BATCH:LIN_STRUCT_CODE" & vbCrLf _
; "$DATUM N" & vbCrLf _
; "$DTYPE BATCH:LIN_STRUCT_DESC" & vbCrLf _
; "$DATUM Sense Strand: " & Cells(I, "C").Value; ";" & "
Antisense Strand:" & vbCrLf _
; Cells(I, "D").Value & ";", vbCrLf _
; "$DTYPE BATCH:pRODUCER(1):pRODUCER" & vbCrLf _
; "$DATUM " & Cells(I, "R").Value & ";", vbCrLf _
; "$DTYPE BATCH:pREP_DESCR" & vbCrLf _
; AVARSTRING & vbCrLf _
; FVARSTRING & vbCrLf _
; EVARSTRING & vbCrLf _
; "$DTYPE BATCH:GENERIC_NAME(1):GENERIC_NAME" & vbCrLf _
; "$DATUM " & Cells(I, "B").Value & ";", vbCrLf _
; "$RIREG " & I - 2 & vbCrLf _
Click to expand...
 
P

paul.robinson

Hi
This block of if, then else's is surely missing a few End If's. No idea
where they would go as I don't know if they are being nested or not. I
would recommend nesting your code.
Your error message often comes up when the real problem is with an
If..then..else or a With...end With or some such.

If IsEmpty(NandMVariable) Then
NandMVariable = ""
Else
If IsEmpty(NVariable) Then
NandMVariable = "char(59) & ' ' & char(34) & $DATUM &
char(34) & MVariable & char(59)"
Else
If IsEmpty(MVariable) Then
NandMVariable = "char(59) & ' ' & char(34) & $DATUM &
char(34) & NVariable & char(59)"
Else
NandMVariable = " char(34) & $DATUM & char(34) &
NVariable &
char(34)_char(34) &
MVariable(char(59)"
End If

regards
Paul
 
B

Brett Smith

Anybody's help would be greatly appreciated. Still getting the For Next loop
compile error.
 
B

Brett Smith

Found out what the problem is. Instead of using Else If, I replaced it with
ElseIf and the program operates.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why is my code not working the way I intend it to? 3
I'm trying to do a comparison if statement but it is not working!! 4
Error Handling on my program 5
VBA Coding Help for Beginner 0
Working outlook via vba code with xlsx - error 0
Word macro that can call cells out of Excel for a find and replace 0
using Outlook Redemption - help needed with some code 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0

Top