Referencing a Cell with Select Case or If

[Pinaceous]

Hi All,

I'm trying to reference a cell in vba of

Sheet1.B5

that has the formula

=IF(Sheet2!B6=0,"",Sheet2!B6)

present there.

In using the Select Case &/or If statements, I cannot seem to create the correct criteria whereby the referenced cell of

Sheet1.B5

is truly blank.

If I use:

Sub TestBlank()
If Not Sheet1.Range("B5").Value= "" Then
'If Cell is Occupied Then
    'or Text

    MsgBox "Occupied"
    
    
End If

If Sheet1.Range("B5").Value = "" Then
'If Cell is Blank Then
    'or Text

    MsgBox "Blank"
    '
End If
End Sub

Or

Select Case True
Case Not Sheet1.Range("B5").Value = ""
'If Cell is Occupied Then
    'or Text

    MsgBox "Occupied"
    
    
    
Case Sheet1.Range("B5").Value = ""
'If Cell is Blank Then
    'or Text

    MsgBox "Blank"
    '
    
End Select

This is confusing me.

Can you please lend me your thoughts?

Many thanks,
pinaceous

 

[JoeMo]

Hi All,

I'm trying to reference a cell in vba of

Sheet1.B5

that has the formula

=IF(Sheet2!B6=0,"",Sheet2!B6)

present there.

In using the Select Case &/or If statements, I cannot seem to create the correct criteria whereby the referenced cell of Sheet1.B5 is truly blank.

If I use:

Sub TestBlank()
If Not Sheet1.Range("B5").Value= "" Then
'If Cell is Occupied Then
    'or Text

    MsgBox "Occupied"
    
    
End If

If Sheet1.Range("B5").Value = "" Then
'If Cell is Blank Then
    'or Text

    MsgBox "Blank"
    '
End If
End Sub

Or

Select Case True
Case Not Sheet1.Range("B5").Value = ""
'If Cell is Occupied Then
    'or Text

    MsgBox "Occupied"
    
    
    
Case Sheet1.Range("B5").Value = ""
'If Cell is Blank Then
    'or Text

    MsgBox "Blank"
    '
    
End Select

This is confusing me.

Can you please lend me your thoughts?

Many thanks,
pinaceous

Your question is unclear to me. If the cell you want to reference has a formula in it, it cannot be "truly blank". Can you elaborate on what you want to do?

 

[Pinaceous]

Your question is unclear to me. If the cell you want to reference has a formula in it, it cannot be "truly blank". Can you elaborate on what you want to do?

That is actually my question. If the cell you want to reference has a formula, it cannot be blank?

If that is the case, I'll be looking at a copy paste onto a sheet to try and work around that fact. I was hoping that this cell with a formula could be hidden to appear blank to the code.

Thanks,
Paul

 

[Peter_SSs]

Try

If IsEmpty(Sheet1.Range("B5").Value) Then
  MsgBox "Unoccupied"
Else
  MsgBox "Occupied"
End If

 

[Pinaceous]

Try

If IsEmpty(Sheet1.Range("B5").Value) Then
  MsgBox "Unoccupied"
Else
  MsgBox "Occupied"
End If

Hi Peter_SSs,

For the cell of

Sheet1.Range("B5").Value

I have this cell first being populated by

 xlPasteValues

from a table.

Now the problem is that after the

 xlPasteValues

is executed, the

 If IsEmpty

function does not work because it thinks that something is there a value.

Do you know how I can work around this issue?

Many thanks,
Paul

 

[Peter_SSs]

What do you really have in Sheet1.Range("B5")
- In posts 1 and 3 you said it had a formula
- Now you are saying it has a pasted value

Which is it?

 

[Pinaceous]

Hi Peter,

I messed up where I realized that my copy paste from my table was carrying over values that were hidden.

Your codes are perfect.

Sorry for the ambiguity.

Thanks,
Paul

 

[Peter_SSs]

Glad you have it sorted.