PDA

View Full Version : VBA Use hidden cell to run IF Statement



foxyginger
08-18-2017, 02:39 PM
Warning: This is my first MACRO and it's been evolving in complexity with each SOLVED response.

Currently my MACRO runs great, but the next request will require checking a hidden cell (D12) for either "1" or "2".. then using an IF Statement to either (1) Run Procedure2 and then continue to the next MACRO procedure3 as usual OR (2) Skip Procedure2 and continue straight to Procedure3.

I will start by showing you my entire code thus far, although it will be restructured (as shown in the second data coding).


Dim AttFile As String

Sub RunAllMacros()
Procedure1
Procedure2
End Sub

Sub Procedure1()
Sheets("ETL").Select
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("C7").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DATA"
Application.CutCopyMode = False

Dim Path As String
Dim FileName1 As String
Dim FileName2 As String


Path = "M:\"
FileName1 = Range("C3")
FileName2 = Format(Range("D3").Value, "mm-dd-yyyy")

AttFile = Path & FileName1 & "_" & FileName2 & ".xlsx"
ActiveWorkbook.SaveAs Filename:=AttFile, FileFormat:=xlOpenXMLWorkbook
End Sub


Sub Procedure2()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object

Set OutLookApp = CreateObject("OutLook.Application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = ""
.Subject = "Equity ETL"
.Body = "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add AttFile
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub


SO, what I need is -- A new VBA [Sub Procedure1 () and Sub Procedure2 ()] to be added in front of my already created MACRO, so that is structured in the manner shown below.


Sub Procedure1 ()
'This code will need to look at the current open worksheet (titled "Equity Wire") and then read cell D12 (Row 12 is hidden) -- this could be done by unhiding, reading the value, and then hiding again or any other more efficient manner -- Once it has read cell D12 which will either be "1" or "2", it will need to run an IF statement analysis. If "1", then run procedure2. If "2" skip to procedure3.
End Sub

Sub Procedure2 ()
'This procedure should ONLY run if cell D12 was "1" as stated above. For procedure3, I would need this code to copy the information on the same worksheet ("Equity 'Wire") in a particular range (B47:H76), paste this section to a new workbook (using paste special -- paste values), then convert the newly made workbook to PDF, 'SaveAs the new workbook using:
'Path = "M:\"
'FileName1 = Range("C3")
'FileName2 = Format(Range("D3").Value, "mm-dd-yyyy")
'AttFile = Path & FileName1 & "_" & FileName2 & ".pdf"
'ActiveWorkbook.SaveAs Filename:=AttFile, FileFormat:=xlTypePDF

'and finally, attach the newly created PDF to an e-mail using:

'.To: GAM Cash Mgmt
'.Subject: Equity ETL
'.Body: "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
End Sub

'Everything below will remain the same

Sub Procedure3 ()
Sheets("ETL").Select
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("C7").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DATA"
Application.CutCopyMode = False

Dim Path As String
Dim FileName1 As String
Dim FileName2 As String


Path = "M:\"
FileName1 = Range("C3")
FileName2 = Format(Range("D3").Value, "mm-dd-yyyy")

AttFile = Path & FileName1 & "_" & FileName2 & ".xlsx"
ActiveWorkbook.SaveAs Filename:=AttFile, FileFormat:=xlOpenXMLWorkbook
End Sub


Sub Procedure4()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object

Set OutLookApp = CreateObject("OutLook.Application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = ""
.Subject = "Equity ETL"
.Body = "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add AttFile
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub

Let me know what other information is needed to figure this out. As I stated above, this is my first VBA so speak simply if possible.

Thank you so much!

foxyginger
08-18-2017, 02:45 PM
This is my current TEST File VBA on the structuring of my new VBA Code:


Dim AttFile As String


Sub RunAllMacros()
Procedure1
Procedure2
Procedure3
Procedure4
Procedure5
End Sub


Sub Procedure1 () 'Read Hidden D12 and Perform IF Analysis

End Sub

Sub Procedure2 () 'Create PDF, SaveAs, and Attach to an E-mail .To: GAM Cash Mgmt

End Sub


Sub Procedure3 () 'Format DATA Workbook and SaveAs Cell Content
Sheets("ETL").Select
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("C7").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DATA"
Application.CutCopyMode = False

Dim Path As String
Dim FileName1 As String
Dim FileName2 As String


Path = "M:\"
FileName1 = Range("C3")
FileName2 = Format(Range("D3").Value, "mm-dd-yyyy")

AttFile = Path & FileName1 & "_" & FileName2 & ".xlsx"
ActiveWorkbook.SaveAs Filename:=AttFile, FileFormat:=xlOpenXMLWorkbook
End Sub



Sub Procedure4 () 'Create an Email and Attach .xlsx file – AND IF THERE’S A PDF EMAIL STARTED, attach to that as well
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object

Set OutLookApp = CreateObject("OutLook.Application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = ""
.Subject = "Equity ETL"
.Body = "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add AttFile
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub


Sub Procedure5 () 'Close Unneeded Workbooks—DATA (save changes) and PDF (disregard changes)
End Sub

offthelip
08-18-2017, 02:49 PM
VBa doesn't care whether a cell is hidden or not, you can pick up the values even if it is hidden, so:


if(cells(12,4)=1 then
call procedure2
call procedure3
else
if cells(12,4)=2 then
call procedure3
end if
end if

foxyginger
08-21-2017, 02:35 PM
Great, thank you!


VBa doesn't care whether a cell is hidden or not, you can pick up the values even if it is hidden, so:


if(cells(12,4)=1 then
call procedure2
call procedure3
else
if cells(12,4)=2 then
call procedure3
end if
end if