PDA

View Full Version : [SOLVED] Cell Returns no value even though the cell does contain a value



qitjch
02-18-2016, 01:30 PM
Hello,

I have data entered into cells on a worksheet. When I use a MsgBox to return the value of said cells, the message box displays as 0 even though the cells contains a non zero value. I am clueless as to why this is happening. I've tried looking at the values of multiple cells but it seems to be happening on the entire sheet.



Sub wtf()

MsgBox (ThisWorkbook.Worksheets("Upload & Estimate").Range("F90").Value)

End Sub


Screenshot of cell I am trying to report in MsgBox as a test. You can see the textbox in the corner coming back with a zero value for the highlighted cell.

15440

Any ideas?

p45cal
02-18-2016, 03:41 PM
Is the sheet you're showing really called Upload & Estimate (no extra spaces etc.)
Is the column indeed F (can't tell from your screen shot)?
Is F90 part of a merged area?
Is there more than one workbook open with similarly named sheets? (ThisWorkbook is the one with the code in, is there another one?)
Change the macro to read:
MsgBox Selection.value, Selection.address
then select the cell and run it.
Do you get what you expect? Is the address $F$90?

SamT
02-18-2016, 04:51 PM
what happens without the parens?

MsgBox ThisWorkbook.Worksheets("Upload & Estimate").Range("F90")

qitjch
02-19-2016, 08:07 AM
Oh jeez, now I feel dumb :)

I took your advice and used MsgBox Selection.value, Selection.address to check a few cells on the Sheet in question. Both the addresses and values came back correctly. I then double checked the sheet name and found out that there was actually an extra space at the end of its name. In addition, there was a hidden sheet named "Upload & Estimate" already that I had no idea even existed in the workbook.

Once I removed the space and deleted the extra sheet everything started to work properly.

And if you were curious, here is the larger macro this was for:


Sub Upload_Estimate_Check()

Dim sht As Worksheet

Set sht = ActiveWorkbook.Sheets("Upload & Estimate ")

If Round(sht.Range("F92").Value, 0) = 0 Then
MsgBox ("Volume figures balance on Upload & Estimate tab!")
If IsUserFormLoaded("UserForm1") = True Then
MsgBox (UserForm1.Name & " Found. Press OK to delete.")
End If
Else
MsgBox ("The Upload & Estimate tab does not balance with the IND-External tab." _
& vbNewLine & vbNewLine & "Variance of: " & Format(Round(sht.Range("F92"), 0), "$#,##0;($#,##0)") _
& vbNewLine & vbNewLine & "Press OK to continue.")[/INDENT]UserForm1.Show vbModeless
End If

End Sub


As always, thanks for the quick reply and help! Much appreciated

snb
02-19-2016, 10:05 AM
Sub Upload_Estimate_Check()
y= round(ActiveWorkbook.Sheets("Upload & Estimate ").Range("F92").Value, 0)
c00="The Upload & Estimate tab does not balance the IND-External tab."

if y = 0 Then
MsgBox replace(c00," not "," ")
If IsUserFormLoaded("UserForm1") = True Then MsgBox (UserForm1.Name & " Found. Press OK to delete.")
Else
MsgBox c00 & replace("~Variance of: " & Format(y, "$#,##0") & "~Press OK to continue."),"~",string(2,vbcrlf))
UserForm1.Show vbModeless
End If
End Sub