Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering value into cell via VB code if cell is empty
Hi there everyone;
Again thanks a lot in advance for the time and help. This time the question is; I'm trying to write a code which should check the cell if it's empty or not. If it's empty, code should write the current time into cell. If the cell is not empty, code should go to the other cell to check if that one is empty or not. Here is my unsuccessful code; ________________________________________ Function zaman() ActiveCell = Time() ActiveCell.NumberFormat = "hh:mm" End Function __________________________________________ Sub ar1open() Range("D9").Select If D9 = "" Then D9 = zaman Else Range("D10").Select If D10 = "" Then D10 = zaman End If End If End Sub Best Regards. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering value into cell via VB code if cell is empty
maybe something like this, but i'm not sure what you want to do when both cells
are populated Sub ar1open() With Range("D9") If .Value = "" Then .Value = Format(Time(), "hh:mm") Exit Sub ElseIf .Offset(, 1).Value = "" Then .Offset(, 1).Value = Format(Time(), "hh:mm") End If End With End Sub -- Gary "taco" wrote in message ... Hi there everyone; Again thanks a lot in advance for the time and help. This time the question is; I'm trying to write a code which should check the cell if it's empty or not. If it's empty, code should write the current time into cell. If the cell is not empty, code should go to the other cell to check if that one is empty or not. Here is my unsuccessful code; ________________________________________ Function zaman() ActiveCell = Time() ActiveCell.NumberFormat = "hh:mm" End Function __________________________________________ Sub ar1open() Range("D9").Select If D9 = "" Then D9 = zaman Else Range("D10").Select If D10 = "" Then D10 = zaman End If End If End Sub Best Regards. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering value into cell via VB code if cell is empty
This should work for you. This code finds the last row that has a value in
it in Sheet1 in Col. A. It then applies the time to all blank cells. Sub TimeInCell() Dim LastRow As Long Dim myRange As Range Dim cell As Range 'finds last row in Sheet1 Col. A LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 'sets the range to apply times Set myRange = Sheets("Sheet1").Range("A2:A" & LastRow) 'applies time to all empty cells For Each cell In myRange If IsEmpty(cell) Then cell = Format(Now, "hh:mm AM/PM") Next cell End Sub Hope this helps! If so click "yes" below. -- Cheers, Ryan "taco" wrote: Hi there everyone; Again thanks a lot in advance for the time and help. This time the question is; I'm trying to write a code which should check the cell if it's empty or not. If it's empty, code should write the current time into cell. If the cell is not empty, code should go to the other cell to check if that one is empty or not. Here is my unsuccessful code; ________________________________________ Function zaman() ActiveCell = Time() ActiveCell.NumberFormat = "hh:mm" End Function __________________________________________ Sub ar1open() Range("D9").Select If D9 = "" Then D9 = zaman Else Range("D10").Select If D10 = "" Then D10 = zaman End If End If End Sub Best Regards. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering value into cell via VB code if cell is empty
I thought about this code. This code is a little faster and more efficient.
Option Explicit Sub TimeInCell() Dim LastRow As Long Dim myRange As Range 'finds last row in Sheet1 Col. A LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 'sets the range to apply times Set myRange = Sheets("Sheet1").Range("A2:A" & LastRow).SpecialCells(xlCellTypeBlanks) 'applies time to all empty cells myRange = Format(Now, "hh:mm AM/PM") End Sub Hope this helps! If so please click "yes" below. -- Cheers, Ryan "RyanH" wrote: This should work for you. This code finds the last row that has a value in it in Sheet1 in Col. A. It then applies the time to all blank cells. Sub TimeInCell() Dim LastRow As Long Dim myRange As Range Dim cell As Range 'finds last row in Sheet1 Col. A LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 'sets the range to apply times Set myRange = Sheets("Sheet1").Range("A2:A" & LastRow) 'applies time to all empty cells For Each cell In myRange If IsEmpty(cell) Then cell = Format(Now, "hh:mm AM/PM") Next cell End Sub Hope this helps! If so click "yes" below. -- Cheers, Ryan "taco" wrote: Hi there everyone; Again thanks a lot in advance for the time and help. This time the question is; I'm trying to write a code which should check the cell if it's empty or not. If it's empty, code should write the current time into cell. If the cell is not empty, code should go to the other cell to check if that one is empty or not. Here is my unsuccessful code; ________________________________________ Function zaman() ActiveCell = Time() ActiveCell.NumberFormat = "hh:mm" End Function __________________________________________ Sub ar1open() Range("D9").Select If D9 = "" Then D9 = zaman Else Range("D10").Select If D10 = "" Then D10 = zaman End If End If End Sub Best Regards. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering value into cell via VB code if cell is empty
Hi Gary;
Thanks a lot for the help. It works, just one more problem. In case D9 is full, next cell should be D10. Now it's moving to E9. I've tried .next instead of .offset but result was the same. Any Idea?? Best Regards; taco "Gary Keramidas" wrote: maybe something like this, but i'm not sure what you want to do when both cells are populated Sub ar1open() With Range("D9") If .Value = "" Then .Value = Format(Time(), "hh:mm") Exit Sub ElseIf .Offset(, 1).Value = "" Then .Offset(, 1).Value = Format(Time(), "hh:mm") End If End With End Sub -- Gary "taco" wrote in message ... Hi there everyone; Again thanks a lot in advance for the time and help. This time the question is; I'm trying to write a code which should check the cell if it's empty or not. If it's empty, code should write the current time into cell. If the cell is not empty, code should go to the other cell to check if that one is empty or not. Here is my unsuccessful code; ________________________________________ Function zaman() ActiveCell = Time() ActiveCell.NumberFormat = "hh:mm" End Function __________________________________________ Sub ar1open() Range("D9").Select If D9 = "" Then D9 = zaman Else Range("D10").Select If D10 = "" Then D10 = zaman End If End If End Sub Best Regards. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering value into cell via VB code if cell is empty
Hi Ryan;
Thanks you very much for your time and help. Your code looks perfect but for one single body sheet. I have 42 sections in one sheet. Every section have 8 rows individually. When related button will be clicked, code behind should check just those 8 rows and should fill first empty cell. If all 8 is filled should give a warning. I don't have problem with warning part, just I couldn't put the code to check those rows one by one. Best Regards; taco "RyanH" wrote: I thought about this code. This code is a little faster and more efficient. Option Explicit Sub TimeInCell() Dim LastRow As Long Dim myRange As Range 'finds last row in Sheet1 Col. A LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 'sets the range to apply times Set myRange = Sheets("Sheet1").Range("A2:A" & LastRow).SpecialCells(xlCellTypeBlanks) 'applies time to all empty cells myRange = Format(Now, "hh:mm AM/PM") End Sub Hope this helps! If so please click "yes" below. -- Cheers, Ryan "RyanH" wrote: This should work for you. This code finds the last row that has a value in it in Sheet1 in Col. A. It then applies the time to all blank cells. Sub TimeInCell() Dim LastRow As Long Dim myRange As Range Dim cell As Range 'finds last row in Sheet1 Col. A LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 'sets the range to apply times Set myRange = Sheets("Sheet1").Range("A2:A" & LastRow) 'applies time to all empty cells For Each cell In myRange If IsEmpty(cell) Then cell = Format(Now, "hh:mm AM/PM") Next cell End Sub Hope this helps! If so click "yes" below. -- Cheers, Ryan "taco" wrote: Hi there everyone; Again thanks a lot in advance for the time and help. This time the question is; I'm trying to write a code which should check the cell if it's empty or not. If it's empty, code should write the current time into cell. If the cell is not empty, code should go to the other cell to check if that one is empty or not. Here is my unsuccessful code; ________________________________________ Function zaman() ActiveCell = Time() ActiveCell.NumberFormat = "hh:mm" End Function __________________________________________ Sub ar1open() Range("D9").Select If D9 = "" Then D9 = zaman Else Range("D10").Select If D10 = "" Then D10 = zaman End If End If End Sub Best Regards. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering value into cell via VB code if cell is empty
Hi Gary;
Thank you very much. Ok, I've found it. I've changed the offset from the column to row offset as ".offset(1)". Now everything seems allright. Best Regards; taco "Gary Keramidas" wrote: maybe something like this, but i'm not sure what you want to do when both cells are populated Sub ar1open() With Range("D9") If .Value = "" Then .Value = Format(Time(), "hh:mm") Exit Sub ElseIf .Offset(, 1).Value = "" Then .Offset(, 1).Value = Format(Time(), "hh:mm") End If End With End Sub -- Gary "taco" wrote in message ... Hi there everyone; Again thanks a lot in advance for the time and help. This time the question is; I'm trying to write a code which should check the cell if it's empty or not. If it's empty, code should write the current time into cell. If the cell is not empty, code should go to the other cell to check if that one is empty or not. Here is my unsuccessful code; ________________________________________ Function zaman() ActiveCell = Time() ActiveCell.NumberFormat = "hh:mm" End Function __________________________________________ Sub ar1open() Range("D9").Select If D9 = "" Then D9 = zaman Else Range("D10").Select If D10 = "" Then D10 = zaman End If End If End Sub Best Regards. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering Values and Updating Next Empty Cell in a Range | Excel Worksheet Functions | |||
Entering a Value & Updating the Next Empty Cell in a Range | Excel Worksheet Functions | |||
code to go down a column and find the last cell with data before an empty cell | Excel Programming | |||
What is the Chr() code for Alt+Enter in a cell when entering text? | Excel Discussion (Misc queries) | |||
Formula is entering a default time when it comes across an empty cell.. | Excel Worksheet Functions |