current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I have a big excel file with different dates on Column B (it starts at row 7 and jump to the row 9.. e.g June6th - B7, June 7th - B9, June 8th - B11). I want to have a macro that locate the first empty cell (jumping 1 row.. for example cell B7 has a date on it, so the macro should verify if the cell B9 is empty, not B8) and then ask the user to input the Date on this cell. Is it possible?

Thank you!

Sub BallMillInspection()

Dim BallMillNumber As String

' IDENTIFY WHAT BALL MILL
BallMillNumber = InputBox("Enter Ball Mill Number, e.g. 1")
If BallMillNumber = vbNullString Then Exit Sub
If BallMillNumber > 5 Then
MsgBox "This Ball Mill does not exist!"
End If

MsgBox "You are starting the inspection of Ball Mill " & BallMillNumber

    Dim vSheet As Worksheet
Set vSheet = Sheets("BM " & BallMillNumber)

With vSheet

Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String
sourceCol = 2 'column B has a value of 2
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row 'for every row, find the first blank cell
    For currentRow = 7 To rowCount Step 2
    currentRowValue = Cells(currentRow, sourceCol).Value
    If IsEmpty(currentRowValue) Or currentRowValue = "" Then
        Cells(currentRow, sourceCol) = InputBox("Enter Date")
    End If
Next

End With

End Sub
share|improve this question
    
Yes, it is possible... –  KazimierzJawor Jun 12 '13 at 18:01
    
@KazJaw Do you have an example? –  user2479465 Jun 12 '13 at 18:08
    
You need to show us something you tried and then we help you get it right. No one is just going to do it for you unless they are in a really super good mood or bored out of their mind. –  Sorceri Jun 12 '13 at 18:09
    
check this like for 'how to ask?' and 'how to search?' in StackOverflow... –  KazimierzJawor Jun 12 '13 at 18:10
    
@KazJaw I can't answer my own question now, so.. I will try using a comment. Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer Dim currentRowValue As String sourceCol = 2 'column B has a value of 2 rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row 'for every row, find the first blank cell and select it For currentRow = 1 To rowCount currentRowValue = Cells(currentRow, sourceCol).Value If IsEmpty(currentRowValue) Or currentRowValue = "" Then Cells(currentRow, sourceCol)=inputbox("Enter Date") End If Next –  user2479465 Jun 12 '13 at 18:18

1 Answer 1

up vote 0 down vote

In your loop just add step 2 to move two cells

Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String
sourceCol = 2 'column B has a value of 2
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row 'for every row, find the first blank cell and select it
'added Step 2
For currentRow = 1 To rowCount Step 2
    currentRowValue = Cells(currentRow, sourceCol).Value
    If IsEmpty(currentRowValue) Or currentRowValue = "" Then
        Cells(currentRow, sourceCol) = InputBox("Enter Date")
    End If
Next
share|improve this answer
    
When I changed the currentRow=7 (because it start at row 7) it does not work anymore. I just want to locate the first empty cell starting at B7 and then enter the date –  user2479465 Jun 12 '13 at 18:51
    
can you state a bit more of whats not working. I ran this with dates starting at B7 (skipping every other row and removing some dates in the odd rows) and it worked correctly. I even removed the B7 date and it still ran to completion. –  Sorceri Jun 12 '13 at 19:11
    
Hey.. thanks for you help! I edited my question again with the whole code so you can see what I am doing. I have to, first, read what worksheet (SheetBM) that the user will input a date. Then, using this worksheet.. the user will input a date in the first empty cell starting at B7. I don't know, also, how to select the worksheet that was previously selected (I know there is a "with" command but it always give me a error msg)/ –  user2479465 Jun 12 '13 at 19:22
    
It just does not run. It reads the first part of the code, but when it reaches the "empty cell finder" part, it stops with no error msg –  user2479465 Jun 12 '13 at 19:45
    
When I run using currentRow=1.. it runs (But asks to enter the date twice). But the dates that are already filled start at row 7. When I change currentRow=7... It does not run :( –  user2479465 Jun 12 '13 at 19:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.