Previous Topic Next Topic

Macro to test Null values

Closed
mrw - Aug 19, 2009 at 11:46 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 19, 2009 at 09:31 PM
Hello,

I have a spreadsheet where if I enter a numeric value between 3000 and 7000 (excluding values between 6000 and 6500) it checks to determine whether values in the same row are null. If they are, a message box appears indicating that null values are present). For exaple.

For Example: For any row if value in the column 'C' is NUMERIC and its value not between '6000' and '6500' then values in columns 'G' and 'R' can not be NULL

If anyone can assist that would be great !

Thanks,

Jenifer

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Aug 19, 2009 at 09:31 PM
try this macro
Assumptions.
row 1 is having column headings. data is from row 2. there is no gap in column A 

Sub test()
Dim j As Integer, k As Integer
 j = Cells(1, 1).End(xlDown).Row
For k = 2 To j
If IsNumeric(Cells(k, "c")) And Cells(k, "c") < 6000 Or Cells(k, "c") > 8600 _
     And Cells(k, "G") = 0 Or Cells(k, "R") = 0 Then _
    MsgBox "null values are present"
Next k
End Sub

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

Hello,

i want to sum particular cells when a number consists in a cell

eg:-

=IF($B$81:$BH$81=7=TRUE,SUM($B$77:$BH$77),0)

it calculates all b77 to bh77 if any cell b81 to bh81 contains 7
i want a formula to calculate like if

b81=7, x81=7, ...... , bh81=7....

sum(b77+x77+......+bh77)

please help me to solve this issue..

2 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

Hello, and good morning, I have a cell with a dropdown box, to select a certain text. When I select the type of incident from a drop down box, I would like the adjacent cell to poulate with the number (eg Minor injury = 4) I have to do it for the three blank columns  Any help will be much appreciated. Thanks, Mitch.

20 responses
Excel function question
1234ah -
ac3mark -

Hello,

Which spreadsheet function in Excel will display 6 as the output in the following formula?

=(36)

3 responses
Clear cell contents based on value of another cell range using VBA
Tbonekiller -
Tbonekiller -

I'm new to all of this and I'm stumbling through as best as I can so forgive me if I don't explain this very well.

I have a workbook that has over 180 worksheets in it.

I store ongoing data on each worksheet in cells a6:i6 through a2506:i2506.

At the start of the new year I only need to keep "Active" data.

I want to be able to compare cell b6 through b2506 individually and make sure it doesn't match ANY cell in the range k8:k508 collectively and then clear contents of range a:i for each row b6:b2506 that doesn't match.

I can't use a simple compare b to k because column k is a consolidated (removing duplicates) list of...

6 responses
Return Seq Values Until Blank value then return different set of seq values
lorieb63 -
TrowaD -

Hello,
What is a formula to return a specific set of values in sequential cells until the reference sequence returns a blank value then start returning values from a different sequence?

Example:

if A2 is not blank, return value in A2 then sequential as A3, A4 etc. else return value starting in B1, then sequential as B2, B3 etc…

1 response