vba problem

[stilgar]

i've got a sheet that i've wrote a heap of code for but i'm stuck on a section of it. please note i'm only new to vba

i've got data in cells d27 and d30 that gets entered in by a macro when a button is clicked.

this makes cell d33 display text when either of those 2 cells are populated

what i want is code so when cell d27 and d30 have nothing displayed in them then cell d33 will display nothing aswell

hope i explained that well enough

 

[Sektor]

@if(and(isblank(d30),isblank(d27)),"","your_text")

 

[stilgar]

thanks but i'm not sure i understand.

is this to be used in vb? it looks like a formula is all

 

[Sektor]

Yes, it's formula. You need checking in VBA? IMHO, formula is easiest way to handle this.

 

[John Davis]

i've got a sheet that i've wrote a heap of code for but i'm stuck on a section of it. please note i'm only new to vba

i've got data in cells d27 and d30 that gets entered in by a macro when a button is clicked.

this makes cell d33 display text when either of those 2 cells are populated

what i want is code so when cell d27 and d30 have nothing displayed in them then cell d33 will display nothing aswell

hope i explained that well enough

Do you mean something like this?

If Range("D27").Value = "" and Range("D30").Value = "" Then Range("D33").Value = ""

 

[stilgar]

yeah thats great thanks! how do i have it so that it runs constantly though?

 

[AMAS]

Hi,

You need a loop. Now of course what are you looping (specific ranges, columns, etc.) So it always going to be row 27 and 30, but different columns?

abousetta

 

[Michael M]

Stilgar
Maybe you could post your code and one of us can show you where to place the line John has provided.

 

[stilgar]

yeah thats it

 

[stilgar]

ok well the code that runs in that cell is:

Sub Calculatefaceplate()
Dim sPartial As String
Dim sDuctPartNumber As String
Dim sCell As String

sDuctPartNumber = "PL"

Select Case Range("B7").Value
Case "35mm x 125mm Skirting Duct": sPartial = "125": sCell = "D33"
Case "35mm x 150mm Skirting Duct": sPartial = "150": sCell = "D33"
Case "50mm x 150mm Skirting Duct": sPartial = "150": sCell = "D33"
Case "50mm x 200mm Skirting Duct": sPartial = "200": sCell = "D33"
Case Else: sPartial = "XXXXX": MsgBox "Invalid dimensions face 1": GoTo End_Sub
End Select

sDuctPartNumber = sDuctPartNumber & sPartial

Select Case Range("b21").Value
Case "DROP-IN LID SELECTED": sPartial = "DFC": sCell = "D33"
Case "CLIP-ON LID SELECTED": sPartial = "CFC": sCell = "D33"
Case Else: sPartial = "XXXXX": MsgBox "Invalid dimensions face 2": GoTo End_Sub
End Select

sDuctPartNumber = sDuctPartNumber & sPartial


Select Case Range("B18").Value
Case "BLACK": sPartial = "B"
Case "WHITE": sPartial = "W"
Case "OPAL GREY": sPartial = "O"
Case "NATURAL ANODISED": sPartial = "N"
Case "POWDERCOATED": sPartial = "S"
Case Else: sPartial = "X": MsgBox "Invalid color": GoTo End_Sub
End Select
sDuctPartNumber = sDuctPartNumber & sPartial

If InStr(sDuctPartNumber, "X") > 0 Then sDuctPartNumber = ""

Range(sCell) = sDuctPartNumber
End_Sub:
End Sub


i've used jons code in a seperate sub but i have to run it using a control button. so i have to click it to update everytime

really the sheet is at the stage where if i could make a new sub that runs cirtian macro's depending on what conditions are met then that should solve it. this macro would need to run in a loop though.

the sheets uses some drop down menu's and others are run when a control button is clicked. eg: "would you require this part?" click yes or no and the cell displays the correct part number

i'm still new to vb so slowly learning