0

I currently have a worksheet that I have multiple people filling out every day. There are 4 columns that the users fill out: C, E, H, & J (all numerical values, one row per day of the month.)

The users fill in C, E, & H every day no matter what, but a lot of days there is no value to put in column J. I need the value in J to be set to 0 if the user doesn't enter anything. Of course it would be easier to just have the users enter 0, but I'm working with a complicated group of people here.

Anyway, I want to use a macro that runs automatically when the user clicks the save button (before it actually saves, of course), and have it do the following: (I am more familiar with php, so I'm just typing this out how I'm familiar - I'm sure my syntax is incorrect)

Foreach Row
  If "column A" != "" {
   If "column J" != "" {
    //Everything is good, continue on...
   } else {
    CurrentRow.ColumnJ.value == 0
   }//Value has been set - continue loop
  }
  //column A is blank, this day hasn't come yet - quit looping here
End Foreach

If anyone could help me out with this I'd appreciate it. With some research, this is what I've come up with so far, and now I'm stuck…

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Dim curCell As Range
 'Labor Flow Sheet'.Select

 For Each curCell in Range( ???? )
  If curCell.Value = "" Then
   ???????
  End If
 Next curCell
End Sub

Thanks in advance!

Community
  • 1
  • 1
Dru Darby
  • 1
  • 1
  • How do you know if they haven't entered anything? Is there something in column A or B that indicates the date or the current row? BTW, we're all working with complicated groups of people. :-) – Jon Crowell Aug 23 '13 at 14:14
  • LOL Glad I'm not alone. COL B is MTD Sales dollars, COL A calculates the Daily Sales dollars by subtracting yesterday's sales dollars from Today's MTD. I have an IF statement in column A that assigns a value of "" (nothing) if the user hasn't entered anything in COL B, otherwise it does the Daily Sales calculation. I could go off of either one of these columns, really. I just need to check if the cell value is NULL or EMPTY or whatever the proper word for "" is in Excel world. – Dru Darby Aug 23 '13 at 15:51

2 Answers2

1

See this link about finding the right range, and as for the question marks inside the If statement, you would want to put

curCell.Value = 0
Community
  • 1
  • 1
Taylor Tvrdy
  • 163
  • 1
  • 11
0

For the question marks in your statement

For Each curCell in Range( ???? )

Solution 1:
To find the full range you're working with, you'll need to use a column that is filled out each day. You mentioned columns C, E, and H were filled out every day. Using one of those columns (let's pick C for the example here), you could find the range by using the .end method. This goes out either up down left or right from a range until it doesn't find any data. So,

Range("J1", Range("C1").End(xlDown)).Select

will select all cells from J1 (or whatever column is the last in your sheet) to the bottom-most cell containing data in column C, automatically.

Solution 2:
Manually put in the range. For example, to choose A1 to J300:

Range("A1", "J300").Select
Aaron Thomas
  • 5,054
  • 8
  • 43
  • 89
  • LOVE IT! Thanks so much for helping me select the proper range automatically! Any input on how to check for values in the C column and put in a 0 on the J column if necessary? – Dru Darby Aug 23 '13 at 15:00
  • If you liked, please vote up! I think the code you first posted will work fine now with some minor tweaks - I'll try to post later. – Aaron Thomas Aug 23 '13 at 15:21
  • I tried to vote up, but apparently I'm too New, LOL. I will do it as soon as I get enough points. My problem with the looping & the If statements is the syntax, I have the concept down. If you get some time, I appreciate it! – Dru Darby Aug 23 '13 at 15:36
  • Hey any advice on checking & replacing those values? Thank you!! – Dru Darby Aug 26 '13 at 16:32
  • Sure, I'd suggest looking into the range.replace method for replacing values. You might consider posting another question for details. BTW, you might also check out superuser.com - it's a sister site to this one, and more geared towards beginning questions with excel (you can still use excel and os-x tags). – Aaron Thomas Aug 29 '13 at 12:30
  • Sweet, I'll check it out. I appreciate the input @Aaron – Dru Darby Aug 30 '13 at 17:05