SMelander
09-07-2018, 04:49 PM
Hello all!
I am brand new to this forum and very new to Visual Basic and Userforms. I am stumbling across a problem that I cannot easily fix myself. Apologies in advance if I am not using all the correct terminology, as it's all new to me.
I am creating a Userform for student data entry. Their data sheets may not be complete, as there may be more fields on the data sheet (and thus the Userform) than there is actual data. I want certain sets of my data to write to a new row, but ONLY if there is a blank - no sense in making new rows for nothing.
I tried adding an "if" statement, as you can see below, but it still writes to new rows even though there is nothing between my "else" and "end if." I hope my comments below make more sense of what I am trying to do.
Any and all help appreciated!
___________________________________________________________________________ _______________________________________
Private Sub CommandButton1_Click()
ws As Worksheet
Dim LastRow As Long
Set ws = Sheets("TREE - QUADRANT - QUANTITY")
'TREE SPECIES 1- from drop down
'IF statement to only write the cell if there is a species common name listed
If Not IsEmpty(ComboBox5.Text) Then
'finds the last blank row
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
'add the site data into the first few columns of the row
ws.Range("A" & LastRow).Value = ComboBox2.Text 'adds the site code into A
ws.Range("B" & LastRow).Value = TextBox7.Text 'adds the date into B
ws.Range("C" & LastRow).Value = "10T" 'adds the 10T UTM Zone into C
ws.Range("D" & LastRow).Value = TextBox8.Text 'adds the Easting into D
ws.Range("E" & LastRow).Value = TextBox9.Text 'adds the Northing into E
ws.Range("F" & LastRow).Value = TextBox11.Text 'adds the school name into F
ws.Range("G" & LastRow).Value = TextBox10.Text 'adds the team member names into G
ws.Range("H" & LastRow).Value = TextBox12.Text 'adds the notes about the field site into H
'add the data about the trees into columns I-N
ws.Range("I" & LastRow).Value = ComboBox5.Text 'common name
ws.Range("J" & LastRow).Value = ComboBox15.Text 'scientific name
ws.Range("K" & LastRow).Value = TextBox15.Text 'quantity in NE
ws.Range("L" & LastRow).Value = TextBox16.Text 'quantity in SE
ws.Range("M" & LastRow).Value = TextBox17.Text 'quantity in SW
ws.Range("N" & LastRow).Value = TextBox18.Text 'quantity in NW
Else
End If
'TREE SPECIES 2- from drop down
'IF statement to only write the cell if there is a species common name listed
If Not IsEmpty(ComboBox6.Text) Then
Dim LastRow As Long
'finds the last blank row
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
'add the site data into the first few columns of the row
ws.Range("A" & LastRow).Value = ComboBox2.Text 'adds the site code into A
ws.Range("B" & LastRow).Value = TextBox7.Text 'adds the date into B
ws.Range("C" & LastRow).Value = "10T" 'adds the 10T UTM Zone into C
ws.Range("D" & LastRow).Value = TextBox8.Text 'adds the Easting into D
ws.Range("E" & LastRow).Value = TextBox9.Text 'adds the Northing into E
ws.Range("F" & LastRow).Value = TextBox11.Text 'adds the school name into F
ws.Range("G" & LastRow).Value = TextBox10.Text 'adds the team member names into G
ws.Range("H" & LastRow).Value = TextBox12.Text 'adds the notes about the field site into H
'add the data about the trees into columns I-N
ws.Range("I" & LastRow).Value = ComboBox6.Text 'common name
ws.Range("J" & LastRow).Value = ComboBox16.Text 'scientific name
ws.Range("K" & LastRow).Value = TextBox20.Text 'quantity in NE
ws.Range("L" & LastRow).Value = TextBox21.Text 'quantity in SE
ws.Range("M" & LastRow).Value = TextBox22.Text 'quantity in SW
ws.Range("N" & LastRow).Value = TextBox23.Text 'quantity in NW
Else
End If
'there are other data field but for now I will pretend it is just 2
Unload Me
End Sub
I am brand new to this forum and very new to Visual Basic and Userforms. I am stumbling across a problem that I cannot easily fix myself. Apologies in advance if I am not using all the correct terminology, as it's all new to me.
I am creating a Userform for student data entry. Their data sheets may not be complete, as there may be more fields on the data sheet (and thus the Userform) than there is actual data. I want certain sets of my data to write to a new row, but ONLY if there is a blank - no sense in making new rows for nothing.
I tried adding an "if" statement, as you can see below, but it still writes to new rows even though there is nothing between my "else" and "end if." I hope my comments below make more sense of what I am trying to do.
Any and all help appreciated!
___________________________________________________________________________ _______________________________________
Private Sub CommandButton1_Click()
ws As Worksheet
Dim LastRow As Long
Set ws = Sheets("TREE - QUADRANT - QUANTITY")
'TREE SPECIES 1- from drop down
'IF statement to only write the cell if there is a species common name listed
If Not IsEmpty(ComboBox5.Text) Then
'finds the last blank row
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
'add the site data into the first few columns of the row
ws.Range("A" & LastRow).Value = ComboBox2.Text 'adds the site code into A
ws.Range("B" & LastRow).Value = TextBox7.Text 'adds the date into B
ws.Range("C" & LastRow).Value = "10T" 'adds the 10T UTM Zone into C
ws.Range("D" & LastRow).Value = TextBox8.Text 'adds the Easting into D
ws.Range("E" & LastRow).Value = TextBox9.Text 'adds the Northing into E
ws.Range("F" & LastRow).Value = TextBox11.Text 'adds the school name into F
ws.Range("G" & LastRow).Value = TextBox10.Text 'adds the team member names into G
ws.Range("H" & LastRow).Value = TextBox12.Text 'adds the notes about the field site into H
'add the data about the trees into columns I-N
ws.Range("I" & LastRow).Value = ComboBox5.Text 'common name
ws.Range("J" & LastRow).Value = ComboBox15.Text 'scientific name
ws.Range("K" & LastRow).Value = TextBox15.Text 'quantity in NE
ws.Range("L" & LastRow).Value = TextBox16.Text 'quantity in SE
ws.Range("M" & LastRow).Value = TextBox17.Text 'quantity in SW
ws.Range("N" & LastRow).Value = TextBox18.Text 'quantity in NW
Else
End If
'TREE SPECIES 2- from drop down
'IF statement to only write the cell if there is a species common name listed
If Not IsEmpty(ComboBox6.Text) Then
Dim LastRow As Long
'finds the last blank row
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
'add the site data into the first few columns of the row
ws.Range("A" & LastRow).Value = ComboBox2.Text 'adds the site code into A
ws.Range("B" & LastRow).Value = TextBox7.Text 'adds the date into B
ws.Range("C" & LastRow).Value = "10T" 'adds the 10T UTM Zone into C
ws.Range("D" & LastRow).Value = TextBox8.Text 'adds the Easting into D
ws.Range("E" & LastRow).Value = TextBox9.Text 'adds the Northing into E
ws.Range("F" & LastRow).Value = TextBox11.Text 'adds the school name into F
ws.Range("G" & LastRow).Value = TextBox10.Text 'adds the team member names into G
ws.Range("H" & LastRow).Value = TextBox12.Text 'adds the notes about the field site into H
'add the data about the trees into columns I-N
ws.Range("I" & LastRow).Value = ComboBox6.Text 'common name
ws.Range("J" & LastRow).Value = ComboBox16.Text 'scientific name
ws.Range("K" & LastRow).Value = TextBox20.Text 'quantity in NE
ws.Range("L" & LastRow).Value = TextBox21.Text 'quantity in SE
ws.Range("M" & LastRow).Value = TextBox22.Text 'quantity in SW
ws.Range("N" & LastRow).Value = TextBox23.Text 'quantity in NW
Else
End If
'there are other data field but for now I will pretend it is just 2
Unload Me
End Sub