VBA Integer coding

[mrsushi]

Hello,

I'm having some difficulties in changing the below vba code which returns a message based on the text in column E, F and G .

The columns are normally numeric so how would i go changing the below code to say if there are non-numeric values, flag this up in the message box?

Many thanks and look forward to hearing any solutions.

Regards
MS



Sub WarningMac()

Dim res As VbMsgBoxResult

Dim instancesO As Long

Dim instancesP As Long

instancesE = WorksheetFunction.CountIf(Columns("E"), "#N/A N/A")

instancesF = WorksheetFunction.CountIf(Columns("F"), "#N/A N/A")

instancesG = WorksheetFunction.CountIf(Columns("G"), "#N/A N/A")



Datestamp = Format(Sheets("SECURITIES").Range("C1"), "ddmmyyyy")

Path = "S:\12PM\"

Filename = " China A Shares Midday"



res = MsgBox(Prompt:="Archive File to be saved as " & Datestamp & Filename & ".xlsx" & vbNewLine & vbNewLine & _

"Found " & instancesE & " Error(s). Please input price on BID" & vbNewLine & vbNewLine & _

"Found " & instancesF & " Error(s). Please input price on ASK" & vbNewLine & vbNewLine & _

"Found " & instancesG & " Error(s). Please input FX rate" & vbNewLine & vbNewLine & _

"Check CELL C1 is correct for ,todays 12PM Pricng Point", Buttons:=vbYesNo + vbExclamation, Title:="China A Shares Midday File")



If res = vbNo Then

MsgBox "File NOT Archived"

Exit Sub

End If

 

[6StringJazzer]

The following expression will return FALSE if there are non-numeric values in column E:

If WorksheetFunction.CountA(Columns("E")) <> WorksheetFunction.Count(Columns("E")) Then

 

[mrsushi]

The following expression will return FALSE if there are non-numeric values in column E:

If WorksheetFunction.CountA(Columns("E")) <> WorksheetFunction.Count(Columns("E")) Then

Thank you

Where would you insert your line of formula into the above code?

 

[6StringJazzer]

Your question was not extremely specific about what result you wanted. I assumed if you were able to write that VBA to begin with you could tailor the condition to how you wanted to use it.

You are putting all the information into one message box for all the errors (even if there are 0 errors). What do you want the message to look like if there are non-numeric values? BTW the way you have defined it, it will double count. Your error string will be counted as a non-numeric value.

 

[mrsushi]

Your question was not extremely specific about what result you wanted. I assumed if you were able to write that VBA to begin with you could tailor the condition to how you wanted to use it.

You are putting all the information into one message box for all the errors (even if there are 0 errors). What do you want the message to look like if there are non-numeric values? BTW the way you have defined it, it will double count. Your error string will be counted as a non-numeric value.

no probs. i think i got it to work


by the way, would you know how to apply a different font colour to say the below output message?

"Check CELL C1 is correct for ,todays 12PM Pricng Point", Buttons:=vbYesNo + vbExclamation, Title:="China A Shares Midday File")

 

[diddi]

i think you would have to make a userform to display you message, then you can do any customising like you are asking

 

[mrsushi]

i think you would have to make a userform to display you message, then you can do any customising like you are asking

have googled it and suggestions seem to be userform. thankyou

 

[mrsushi]

I have one last error which im not too sure about ....the string part of the code isnt showing what i need. what im trying to get done is to match the cell formula to today = if so, the return the message in the messagebox to say "Warning" Date ok

However, its coming up with FALSE. Please see attachments

Any ideas what the code is missing? or need correction?

Many thanks
M



DateCheck = Range("C1").Formula = "IF(WORKDAY(TODAY(),-R[1]C[1])=TODAY(), ""DATE OK"",""CHECK DATE"")"



res = MsgBox(Prompt:="Archive File to be saved as " & Datestamp & Filename & ".xlsx" & vbNewLine & vbNewLine & _

"Found " & instancesE & " Error(s). Please input price on BID (Column E)" & vbNewLine & vbNewLine & _

"Found " & instancesF & " Error(s). Please input price on ASK (Column F)" & vbNewLine & vbNewLine & _

"Found " & instancesG & " Error(s). Please input FX rate (Column G)" & vbNewLine & vbNewLine & _

"WARNING" & DateCheck, Buttons:=vbYesNo + vbExclamation, Title:="China A Shares Midday File")

 

[diddi]

just see if this helps (its a guess)

DateCheck = Range("C1").Formula = "=IF(WORKDAY(TODAY(),-R[1]C[1])=TODAY(), ""DATE OK"",""CHECK DATE"")"

i added an "=" sign