Excel Mac VBA Loop Through Cells and reset some values

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!

Answers


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

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

Need Your Help

App store upload and setting deployment target in iphone

objective-c xcode4 app-store distribution

i have built my app using xcode 4, & its working fine on ios 3.1.3 device if i set my deployment target to 3.1.3 , it is working fine ios 4 and if i set the deployment target to 4 ,my doubt is ...

html 5 geolocation returns 0,0 on blackberry

javascript html5 blackberry

I've developed a web app that uses location detection, which works fine with Android, iPhone, and many desktop browsers; however, the latitude and longitude values are always returned as 0,0 on my

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.