If statement relying on formula

Resolved · Urgent Priority · Version 2013

Tue 10 Jan 2017, 11:07 Delegate Sean said...

Sean has attended:
Excel VBA Introduction course
Excel VBA Intermediate course

If statement relying on formula

Hi guys,

I'm currently working on a database that is for an labeling machine, it contains one master data sheet and multiple different sheets with separate layouts for the labels.

The current way it's been done is that EACH CELL ON EACH SHEET HAS A =REF TO A CELL ON THE MASTER DATA SHEET. Absolute madness!!!

So I'm in a sticky situation as I need to take data from the master sheet and place it in the others, I have no issue with that except I've found that some cells reference other cells that reference MORE cells in a formula...

I've currently got something like below, yet I keep getting a 'end if without block if' error

What needs to happen is that cell F32 needs to contain the sum of F113*(F66/100)

the most important part is that if cell F66 is empty then cell F32 must be as well. I cannot have any #Values in the sheet.

Sub kwfigures()

Range("F32").Select

If Range("F66").Value = True Then ActiveCell.Value = ("=sum(F113*(F66/100)")
If Range("F66").Value = False Then ActiveCell.Value = ""
End If

End Sub

Tue 10 Jan 2017, 16:36 Trainer Doug said...

RE: If statement relying on formula

Hi Sean

Thanks for your question

Try rewriting the statement slightly

Sub kwfigures()

Range("F32").Select

If isnumeric(Range("F66"))= True Then
ActiveCell.Value = "=sum(F113*(F66/100))"
Else: ActiveCell.Value = ""
End If

End Sub

Notes
There's a syntax rule about IF statements where the THEN statement must be at the end of the line. (Unless the whole IF statement can be written all in one line).

I added the IsNumeric function as that returns True if the Activecell is numeric and False if it's not.

See if that does what you are wanting...

Cheers
Doug
STL

Wed 11 Jan 2017, 08:19 Delegate Sean said...

RE: If statement relying on formula

Thanks Doug,

is there a way to only copy the result of the formula when pasting to another sheet?

I currently have the below code but it's just pasting the formula to another sheet which isn't working.

Sub ERPthree()

'Select Master Data Sheet
Sheets("Master Data").Select

'Copy Relevant Cells
Range("F5,F32").Select
Selection.Copy

'Select Erp Label Sheet
Sheets("Erp Label").Select
Range("C2").Select

'Paste Data to Erp Label Sheet
Selection.PasteSpecial Paste:=xlValue, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

'Cancel Marching Ants
Application.CutCopyMode = False

End Sub

Wed 11 Jan 2017, 10:13 Trainer Doug said...

RE: If statement relying on formula

Hi again Sean

Thanks for sending code.

Try replacing Paste: xlValue

with

Paste:=xlPasteValues

It seems to work if you do that, transposing the cells as values to cells C2:D2 on the Erp Label sheet rather than #Ref.

Regards
Doug