VBA Skip Cells If They Have Data In Them And Go To Next Blank?

Question

Forgive me if I am too much of a noobie for this to work, or even make any sense. I'm working on making a database for myself and a friend of mine to use. (These would be two different databases not merged at all.) I'm not starting from scratch so that might be one of my problems. I'm using my work hours sheet (Don't worry, it's just a copy) to "learn/test" what I want to do with VBA. Basically what I want to do is to take the information (value?) of the cells "A3:D3" & "E2" and put those values into their corresponding columns. But I don't want it to over-right code inside the next cell. That's why I put in line 10 and 11. It still doesn't like it.

Also, I was wondering if I could get help on this...And if the information could possibly be date sensitive? i.e. You type in today's hours and remembered you forgot to log yesterday's hours so you type what the date was in the "DateToday" or A3 box and it logs the information in a row dedicated to that date, OR you skip two days of work and you put the date in and VBA skips two rows?

Are these two different requests? And are either possible? Any help is MUCH appreciated!! Thank you in advance!

    Private Sub CommandButton1_Click()
Dim DateToday As Date, TimeIn As Integer, LunchLength As Integer, TimeOut As Integer, HoursToday As Integer
    Worksheets("sheet1").Select
    DateToday = Range("A3")
    TimeIn = Range("B3")
    LunchLength = Range("C3")
    TimeOut = Range("D3")
    HoursToday = Range("E2")
    Worksheets("sheet1").Range("A141").Select
    If Worksheets("sheet1").Range("A141").Offset(1, 0) <> "" Then
    Worksheets("sheet1").Range("A141").End(x1Down).Select
    End If
    ActiveCell.Value = TimeIn
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = LunchLength
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = TimeOut
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = HoursToday
    Worksheets("sheet1").Range("B2:D2").ClearContents
    ThisWorkbook.Save
    Worksheets("sheet1").Range("B2").Select


End Sub

The last section there "Worksheets("sheet1").Range("B2").Select" is more for my second project but would be nice for this too. The second project would be much like this one except me and my friend will be entering part numbers, definitions, part condition, and other stuff. Once again, ANY help is MUCH appreciated!

score 0 · Accepted Answer · edited May 23 '17 at 10:25

0

Untested:

Private Sub CommandButton1_Click()
Dim DateToday As Date, TimeIn As Integer, LunchLength As Integer
Dim TimeOut As Integer, HoursToday As Integer, sht as WorkSheet

    Set sht = ThisWorkbook.Worksheets("sheet1")

    DateToday = sht.Range("A3")
    TimeIn = sht.Range("B3")
    LunchLength = sht.Range("C3")
    TimeOut = sht.Range("D3")
    HoursToday = sht.Range("E2")

    With sht.Cells(Rows.Count,1).End(xlUp).Offset(1,0).EntireRow
        .Cells(1).Value = TimeIn
        .Cells(2).Value = LunchLength
        .Cells(3).Value = TimeOut
        .Cells(4).Value = HoursToday
    End With

    sht.Range("B2:D2").ClearContents
    ThisWorkbook.Save
    sht.Range("B2").Select
End Sub

Note: typically when working in VBA there's no need to Select anything.

edited May 23 '17 at 10:25

Community

1
1

answered Nov 06 '14 at 23:44

Tim Williams

154,628
8
97
125

thanks for the last bit of advice...the code doesnt seem to work though..well it works about half way, it clears the cells but when it puts in the information it only shows one cell the "LunchLength" cell, but it doesnt give the actual time that i put in, and the other cells beside it have information in them, not the correct information but information. also, the cells beside it dont actually display the information it contains, unless you click on it and look in the formula bar there is no information actually in the cell. – LearningExcelAndLovingIt Nov 07 '14 at 16:39
..it might help to know that the cells the VB is taking information from are cells with formulas in them. the formula is an =if formula that basically looks at the cell above it (so B3 looks at B2, ect) and if i type say 1, or 1hr, ect then B3 takes that and looks to another set of cells for a translation of that and takes 1hr or 2 and turns it into 1:00 or 2:00...so i pretty much want the VB to take a "picture" of the information in B3 and put it into the next cell with a value of "". is this possible with VB? – LearningExcelAndLovingIt Nov 07 '14 at 16:48
Try using `Debug.Print TimeIn, LunchLength, TimeOut, HoursToday` and see what you get in the Immediate pane. Difficult for me to suggest anything else without the file. – Tim Williams Nov 07 '14 at 17:54
Where would i put that bit of code? withing the value strip after the .cell(1) ect? – LearningExcelAndLovingIt Nov 07 '14 at 18:16
Just before `With sht.Cells...` - the output will be in the Immediate pane in the VB editor. I just tested the code and it seems to work fine for me. – Tim Williams Nov 07 '14 at 18:35
it shows a 1 for lunch so that's good, but the other cells (date, in, and out) show up as 0 ill see if i can figure out how to show the basic code here – LearningExcelAndLovingIt Nov 07 '14 at 18:43
what exactly is in those cells? You've defined your variables as `Integer`, so there will be some rounding if the cells have fractional values. Try `Double` instead. – Tim Williams Nov 07 '14 at 18:48
this is what the formula for B3 looks like. '=IF(N40<=1,N40,IF(N41<=1,N41,IF(N42<=1,N42,IF(N43<=1,N43,IF(N44<=1,N44,IF(N45<=1,N45,IF(N46<=1,N46,IF(N47<=1,N47,IF(N48<=1,N48,IF(N49<=1,N49,IF(N50<=1,N50,IF(N51<=1,N51,"0:00"))))))))))))' the column "n" looks at the cell B2 and returns the time based on what it sees. so b2 doesnt look at anything but N45 ect looks at b2 and if it sees a match it accepts it and returns the time set in b2. then b3 looks at the n column to see if anything is numerical and if it is than b3 shows what n45 shows in a time format that we normally use. – LearningExcelAndLovingIt Nov 07 '14 at 18:52
well the display format for those cells is time so it will look at the format for excel time, which is one full day = 1 and half a day = .5, so the cell looks for the "excel version" of time and then converts it to 12hr time format – LearningExcelAndLovingIt Nov 07 '14 at 18:55

VBA Skip Cells If They Have Data In Them And Go To Next Blank?

1 Answers1