IF elseif else conditional statement.

[Hardhat]

I have a large messy data sheet that I export form a piece of software. I am trying to compile the date into individual forms that then can be printed into PDF's and send back to the users to as a confirmation.

conference room request worksheet sample link https://www.dropbox.com/s/delxygrtvdjseiu/Hospitality Services Room Set-Up Request..xlsm?dl=0

I have done some coding but I have run into a couple of issues One trying to name the new worksheets from two cells A3 and K3. My current code will pull from A3 but have not found the right syntax for getting information from K3

second I have with my if then else statement I need for two part of the data form. I have a range of cells that can have a room number in one of the cells. only one of the cells will ever have a number in it. I though that an if then elseif statement would be the way to populate the cell in the form but I am having trouble with that. I an writing that in a separate module until I get it to work, then I was going to ad it to the loop. below is the statement thanks in advance.

Sub roomnumber()


If Sheets(1).Range("AH3") = True Then
Activeworksheet = Sheets(2).Range("Ell").Value = ("AH3")

ElseIf Sheets(1).Range("AL3") = True Then
Activeworksheet = Sheets(2).Range("Ell").Value = ("AL3")

ElseIf Sheets(1).Range("AM3") = True Then
Activeworksheet = Sheets(2).Range("Ell").Value = ("AM3")


ElseIf Sheets(1).Range("AN3") = True Then
Activeworksheet = Sheets(2).Range("Ell").Value = ("AN3")


ElseIf Sheets(1).Range("AO3") = True Then
Activeworksheet = Sheets(2).Range("Ell").Value = ("A03")


ElseIf Sheets(1).Range("AP3") = True Then
Activeworksheet = Sheets(2).Range("Ell").Value = ("AP03")

Else
Activeworksheet = Sheets(2).Range("Ell").Value = ("AQ03")




End If


End Sub

 

[Peter_SSs]

Re: Naming the worksheets.
- There are some characters that are not allowed in worksheet names. One of them is "/". So when you are trying to use "Brian Matthews/ Lindsay Mattfolk", that is going to fail.
- Worksheet names can have a maximum of 31 characters so even without the "/" problem, the above name, especially when combined with your 10-digit IDs will be too long. Some of your other names are also getting pretty close to the length limits so if you get longer names in the future that could be a problem too.

You will need to consider how you want to handle both of the above circumstances.


Re: Room number
Your current code is not valid code, so hard to tell exactly what you are trying to do.
Activeworksheet = Sheets(2).Range("Ell").Value = ("AH3")
Can you tell us in words exactly what this line is supposed to be doing if Sheets(1).Range("AH3") = True

 

[ttray33y]

that's what I was thinking too.

 

[Hardhat]

Thank you, I didn't pay close enough attention to the names in K3, I will just stay with the identifier number knowing that will always be different.

If AH3 is true, I want the value of AH3 to be placed in Ell on sheet 2. I may be going about this all wrong. The goal is to go through the range of cells AH3, AL3:AQ3 and place the value found in which ever cells has value into E11. Only one of those cells will ever have a value in it per row. is this clearer?

 

[Fluff]

For your room number try

Sub RoomNum()
   Sheet1.Range("AH3,AL3:AQ3").SpecialCells(xlConstants).Copy Sheet2.Range("E11")
End Sub

 

[Hardhat]

Thank you that is so much easier! When I place it in my loop it faults out. Any suggestion?

Sub duplicateManySheets()
'macro to create one report per employ
'macro duplicates required number of reports
'macro copies required data into reports


Dim counter As Integer 'declare integer variable for loop control




For counter = 1 To 3 ''Sheets(1).Range("f5").Value 'one loop per employee


Sheets(1).Copy After:=Sheets(2) 'copy the sheet


ActiveSheet.Name = Sheets(2).Range("A3").Offset(counter - 1, 0).Value 'name sheet


'ActiveSheet.Name = Sheets(2).Range("A3").Offset(counter - 1, 0).Value & Sheets(2).Range("K3").Value


'position employee information on report
ActiveSheet.Range("B4").Value = Sheets(2).Range("A3").Offset(counter - 1, 0).Value 'respondent #
ActiveSheet.Range("D6").Value = Sheets(2).Range("AE3").Offset(counter - 1, 0).Value 'Meeting Title
ActiveSheet.Range("D7").Value = Sheets(2).Range("K3").Offset(counter - 1, 0).Value  'Form completer
ActiveSheet.Range("D8").Value = Sheets(2).Range("L3").Offset(counter - 1, 0).Value 'Orginizer
ActiveSheet.Range("D9").Value = Sheets(2).Range("M3").Offset(counter - 1, 0).Value 'Dept
ActiveSheet.Range("D10").Value = Sheets(2).Range("T3").Offset(counter - 1, 0).Value 'Phone#
ActiveSheet.Range("D12").Value = Sheets(2).Range("U3").Offset(counter - 1, 0).Value  'Building


Sheet1.Range("AH3,AL3:AQ3").SpecialCells(xlConstants).Value.Copy Sheet2.Range("E12").Offset(counter - 1, 0) 'Roomnum


ActiveSheet.Range("D13").Value = Sheets(2).Range("V3").Offset(counter - 1, 0).Value 'Meeting Date
ActiveSheet.Range("D14").Value = Sheets(2).Range("W3").Offset(counter - 1, 0).Value 'Date Range


ActiveSheet.Range("D15").Value = Sheets(2).Range("AM3").Offset(counter - 1, 0).Value 'Recurring


ActiveSheet.Range("D16").Value = Sheets(2).Range("AA3").Offset(counter - 1, 0).Value 'Setup
ActiveSheet.Range("D17").Value = Sheets(2).Range("AB3").Offset(counter - 1, 0).Value  'setup time
ActiveSheet.Range("D18").Value = Sheets(2).Range("AR3").Offset(counter - 1, 0).Value 'Setup Config
ActiveSheet.Range("D19").Value = Sheets(2).Range("AD3").Offset(counter - 1, 0).Value 'Start time
ActiveSheet.Range("D20").Value = Sheets(2).Range("AC3").Offset(counter - 1, 0).Value 'End time
ActiveSheet.Range("D21").Value = Sheets(2).Range("AF3").Offset(counter - 1, 0).Value '# of Atten
ActiveSheet.Range("D22").Value = Sheets(2).Range("AG3").Offset(counter - 1, 0).Value 'Parking Alloawnces
ActiveSheet.Range("C24").Value = Sheets(2).Range("AT3").Offset(counter - 1, 0).Value 'Parking Alloawnces
ActiveSheet.Range("G3").Value = Sheets(2).Range("C3").Offset(counter - 1, 0).Value 'DATE SUB

 

[Fluff]

You've added a .Value which shouldn't be there.

PS When posting code please use code tags, the # icon in the reply window

 

[Hardhat]

Thank you this works great!! and for the code posting tag.

 

[Fluff]

Glad we could help & thanks for the feedback