Can somebody help me with this code?
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 |
Can somebody help me with this code?
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" wrote: 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 _ |
Can somebody help me with this code?
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 |
Can somebody help me with this code?
OK, so where should the End Ifs go?
" wrote: 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 |
Can somebody help me with this code?
Anybody's help would be greatly appreciated. Still getting the For Next loop
compile error. "Brett Smith" wrote: OK, so where should the End Ifs go? " wrote: 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 |
Can somebody help me with this code?
Found out what the problem is. Instead of using Else If, I replaced it with
ElseIf and the program operates. "Brett Smith" wrote: Anybody's help would be greatly appreciated. Still getting the For Next loop compile error. "Brett Smith" wrote: OK, so where should the End Ifs go? " wrote: 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 |
All times are GMT +1. The time now is 11:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com