0

Please help me, I'm frustrated with excel. Probably because I'm not that good with it. I've been tasked with the following. Take all blank cells in column c, reference the value in the adjacent cell in column b, and then print a statement in the cell.

I tried this function to no avail.

=IF(AND(C3 = "", 0 < B3 < 5000), "Order More", "Don't Order")

I got a circular error. So I thought I must need to use VBA.

Here are the snippets of code I have so far:

Worksheets("Sheet1").Activate

For Each rw In Sheet1.Rows
If rw.Columns("C") = "" and range.rw.Columns("B")

I'm not sure how to make it all work. I don't even know if that's correct. I've been trying to use the internet to help but it's just more confusing.

PA_Commons
  • 269
  • 2
  • 6
  • 14
  • Untested, as I am on mobile, but give this a go: `Range(C3:C1000).SpecialCells(xlCellTypeBlanks).Formula = "IF(AND(B3>0,B3<5000),"Order More","Don't Order")" `. Replace C1000 with your last cell. – Ioannis Jun 08 '14 at 01:57
  • Thank you for the reply, that looks simple, something that I can understand. Everyday though, our excel sheets change and have more or less rows in column C. Somedays it could be hundreds in each sheet, other days maybe 50. – PA_Commons Jun 08 '14 at 04:22
  • In this case, you can [find the last row in the range you use](http://stackoverflow.com/questions/18952362/excel-vba-select-range-at-last-row-and-column), save it in a `Long` variable called `LastRow` and then do `Range("C3:C" & LastRow)` instead. – Ioannis Jun 08 '14 at 12:41
  • Thank you very much for the suggestion. I am indebted to you good sir! – PA_Commons Jun 09 '14 at 05:18
  • I get a compile Error (expected end of statement) when I enter "=IF(AND(B3>0,B3<5000),"Order More","Don't Order")" – PA_Commons Jun 11 '14 at 21:38
  • Yes, when I wrote it I was on mobile. Can you try `"=IF(AND(B3>0,B3<5000),""Order More"",""Don't Order"")"`? – Ioannis Jun 11 '14 at 22:44
  • Or even better (what I prefer) `"=IF(AND(B3>0,B3<5000)," & Chr(34) & "Order More" & Chr(34) & "," & Chr(34) & "Don't Order" & Chr(34) & ")"`. For reference, `Chr(34)="` in ASCII, and `&` is the operator that joins strings. So the above expression is a concatenation of strings `"=IF(AND(B3>0,B3<5000),"` and `"` and `Order More` and `"` and so on.. – Ioannis Jun 11 '14 at 22:50
  • also, upon first using your SpecialCells condition, it won't get the cell ranges correct in the formula. As it works down the range of C cells, for every blank cell, it apparently throws off the number in the formula by one. – PA_Commons Jun 11 '14 at 23:28

2 Answers2

0

I don't think you want VBA.
Your formula should be 

=IF(AND(C3 = "", 0 < B3, B3 < 5000), "Order More", "Don't Order")

If you're getting a circular error, it's because you're putting the formula in B3 or C3. It would logically be in A3 or D3?

dcromley
  • 1,373
  • 1
  • 8
  • 23
  • I think the above solution given by dcromley should work. Test that out. Not sure of the reason for circular error though i am inclined to agree with dcromley as above. – Peekay Jun 08 '14 at 15:09
  • Thanks for the help, I will give it a go tomorrow at work. Much appreciated. – PA_Commons Jun 09 '14 at 05:18
0

Posting this as a response since

  • It is cumbersome to explain it on a comment

  • It is tested and it works for Excel 2010.

    Sub test()

Dim lFirstBlank As Long, lLastRow As Long
Dim rRange As Range

lLastRow = Cells(Rows.Count, 3).End(xlUp).Row
lFirstBlank = _
Range("C3:C" & lLastRow).SpecialCells(xlCellTypeBlanks).Cells(1, 1).Row

Set rRange = Range("C" & lFirstBlank & ":C" & _
    lLastRow).SpecialCells(xlCellTypeBlanks)

rRange.Formula = _
"=IF(AND(B" & lFirstBlank & ">0,B" & lFirstBlank & "<5000)," & Chr(34) & _
"Order More" & Chr(34) & "," & Chr(34) & "Don't Order" & Chr(34) & ")"

rRange.Value = rRange.Value


End Sub

The problem was that we need to find the first blank cell and enter the formula there with the reference relative to that cell (first blank), as I understand from your latest response. For instance, if the first blank cell is C5, then the formula should refer to C5 and B5, not C3. The above code works as expected, assuming that the sheet we are looking at is the ActiveSheet (i.e., it is activated).

EDIT

If we want to remove the dependency on column B, we can add the line

rRange.Value = rRange.Value

This will remove the formula dependencies and will keep only the values (like pasting only values).

The reasons why I did not write the formula as 

=IF(AND(C3 = "", 0 < B3, B3 < 5000), "Order More", "Don't Order")

are that

  • It is going to give us a circular reference error, because we are using the value of C3 to determine the value of C3.
  • Even if we avoid (or ignore) the circular reference, it does not seem to do what you want exactly: you want to to apply the logic 0 < B3 < 5000 to the blank cells as you mention in the question, and return either "Order More" or "Don't Order". So my understanding is that the non-blank cells should not be touched, whereas the above formula will overwrite whatever is written on the non-blank cells and write there "Don't Order".

I hope this helps!

Ioannis
  • 5,238
  • 2
  • 19
  • 31
  • It does help. You've actually inspired me to think more about what I can do with VBA and Macros where I work. I never thought about all the possibilities until recently. Thank You Very Much! – PA_Commons Jun 13 '14 at 18:29
  • Wouldn't it be easier to set variables equal to the value ranges in column B (0 and 5000) and then have each black cell in column C reference return a value If True? For example, When I delete Column B afterward, the cells with the formula have no reference. Is that the best way to avoid that problem? – PA_Commons Jun 16 '14 at 04:53
  • Did you have a typo there? The debugger wouldn't allow = .value the only other idea I had was to copy the columns and paste special into unused columns and then re-paste them back into the original column. But that seems horribly inefficient. – PA_Commons Jun 17 '14 at 06:26
  • Oh yes, there are not any blank cells any more so the above does not work! I added an edit. Basically, we `Dim` a `Range` variable and `Set` it to be the initially blank cells. Then after we fill the cells up, the `Range` we have defined is still the same, so we can do `.Value=.Value`. Yes, no Copy-PasteSpecial is necessary. – Ioannis Jun 17 '14 at 10:04
  • 1
    cool, I'll give it a try. I almost got to what you have above on my own yesterday. this is way more fun that I thought it could be. – PA_Commons Jun 17 '14 at 15:55