Results 1 to 11 of 11

Thread: vba nested if

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I have the following vba code that I need to adapt to a 5 nested if

    Range("A3").FormulaR1C1 = "='" & Worksheets(2).Name & "'!R[-1]C"

    I need to

    Range("A3").FormulaR1C1 =

    IF Worksheets(2).Name & "'!R[-1]C" = A then AA, B then BB..........E then EE

    so that I get 5 different possible outputs.

    How please?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    Range("A3").FormulaR1C1 = "=REPT('" & Worksheets(2).Name & "'!R[-1]C,2)


  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry Hans, I used the A & AA as examples. I need 5 outputs based on 5 possible inputs, almost like a Lookup, but not with a lookup table.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    But seriously:

    ActiveCell.FormulaR1C1 = "=IF('" & Worksheets(2).Name & _
    "'!R[-1]C=""A"",""AA"",IF('" & Worksheets(2).Name & _
    "'!R[-1]C=""B"",""BB"",IF('" & Worksheets(2).Name & _
    "'!R[-1]C=""C"",""CC"",IF('" & Worksheets(2).Name & _
    "'!R[-1]C=""D"",""DD"",IF('" & Worksheets(2).Name & _
    "'!R[-1]C=""E"",""EE"","""")))))"

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts


    Thanks. Can I output the result as a text value instead of formula?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use "ordinary" VBA for that:

    Code:
    Select Case Range("A2")
      Case "A"
    	Range("A3")= "AA"
      Case "B"
    	Range("A3")= "BB"
      Case "C"
    	Range("A3")= "CC"
      Case "D"
    	Range("A3")= "DD"
      Case "E"
    	Range("A3")= "EE"
      Case Else
    	Range("A3")= ""
    End Select

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Aha, so "case" does have it's uses then.

    Finally...

    On the active sheet in A3, I have a product code "AAA, BBB ..... EEE" formatted as text and in A5 a date formatted 13/04/09 as general.

    I need to file save as:

    ActiveWorkbook.SaveAs Filename:= "C:\Users\Nathan\Documents\Files\Product Code\Year\Month\Product Code & Date.xls"

    How can I use the data in A3 & A5 to save to the correct location using the correct file name.

    EG:

    ActiveWorkbook.SaveAs Filename:= "C:\Users\Nathan\Documents\Files\AAA\2009\04. Apr 09\AAA 13.04.09.xls"

    The month folders are formatted as 04. Apr 09 / 05. May 09

    Thanks in advance

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use

    ActiveWorkbook.SaveAs Filename:= "C:\Users\Nathan\Documents\Files\" & _
    Range("A3") & "\" & Year(Range("A5")) & "\" & Format(Range("A5"), _
    "mm. mmm yy") & "\" & Range("A3") & " " & Format(Range("A5"), _
    "dd.mm.yy") & ".xls"

    BTW I'd recommend against using periods . in the folder and file names. Since the extension of a file name is also preceded by a period, other periods in the name can be confusing.

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, Thankyou very much.

    Can I set all of the filepath without the filename as a variable so that I can a. use the filepath in a msgbox and also b. error check to see that all of the expected folders are there, and how would I do the latter? If a certain folder was not there, a. could we create it, or b. how best to deal with the situation?

    If creating folders that are'nt there is not feasible, I would like to still save the file at the \Files\ stage, so maybe an IF check?

    IF all folders available, save as normal and show normal msgbox file saved as .......
    If all folders not available, save at the \files\ location and show error message that file saved at..... instead

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    > Can I set all of the filepath without the filename as a variable

    Of course, why not?

    The code from Post 643607 will check each folder in a path and create it if it doesn't exist.

  11. #11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •