selecting the first empty cell in a row
-
-
-
You'll probably want something like this. (Note: the Range needs to include the column also.) The xlToRight will find the last contiguously filled cell -- hence the offset to move over one more column.
CodeSub Insert1stEmpty() If Len(Range("A16")) = 0 Then Range("A16") = "Answer" Else: Range("A16").End(xlToRight).Offset(0, 1).Value = "Answer" End If End Sub
This code also fills column A if that column is empty.
-
Ok, So i have tried to make this into a function within my Macro...I tried it as both a function and a sub. here is the function i wrote
CodeFunction FillRow(row As Integer, Answer As Variant) If Len(Range("A" & row)) = 0 Then Range("A" & row) = Answer Else: Range("A" & row).End(xlToRight).Offset(0, 1).Value = Answer End If End Function
and when i try to call it from other code by using this line
where 18 is the row i want to insert into the last unfilled cell and Side is a variable that i want to place there.
-
I imagine you were getting a "Type Mismatch" error?
Try this instead:
CodeFunction FillRow(row As Integer, Answer As Variant) If Len(Range("A" & CStr(row))) = 0 Then Range("A" & CStr(row)) = Answer Else: Range("A" & CStr(row)).End(xlToRight).Offset(0, 1).Value = Answer End If End Function
You needed to convert "row" from an integer value to a String value (hence the CStr calls) in order to use it in the Range call that way.
-
A function can only return its value. That is, if you enter "=FillRow(18,Send)" into a cell, that cell is expecting the function to return a value, NOT to fill another cell. A function expects the last line of code to be FillRow = ... and it will return the value of FillRow to the cell.
To fill cell A18, you need to make the code part of a Sub. Perhaps something like the following which allows the user to inpt the row # and cell fill info:
CodeSub FillRow() Dim row As Integer, Answer As Variant row = InputBox("Enter Row Number") Answer = InputBox("Enter Fill Info") If Len(Range("A" & row)) = 0 Then Range("A" & row) = Answer ElseIf Len(Range("B" & row)) = 0 Then Range("B" & row) = Answer Else: Range("A" & row).End(xlToRight).Offset(0, 1).Value = Answer End If End Sub
Note: The ElseIf prevents an error message if column A is filled and B is empty (the End(xlToRight) does not like this situation -- I missed this yesterday.
-
-
Lets see, jmhans, that didnt seem to do the trick, I am getting the error "Expected =" but i changed my routine to typcasting the string as cstr anyway.
Thomach, I am trying to make this batch process a lot of data, so entering the row and answer is not much of an option.
Is there any way to make this work? Thanks guys
-
Thomach was right about functions...as we saw earlier in that other post. I mistakenly forgot that detail. Here's something that might help though
Code
Display MoreFunction FillRow(row As Integer) as Range If Len(Range("A" & CStr(row))) = 0 Then Set FillRow = Range("A" & CStr(row)) Else: Set FillRow = Range("A" & CStr(row)).End(xlToRight).Offset(0, 1).Value End If End Function 'Then, upon calling this function in your Create Sheets macro, you could use: Sub Createheets() ... "AppropriateSheet".Range(Fillrow("rownumber").Address) = Answer ... End Sub
See if that one works.
-
Ok, now its not crashing due to syntax (i think im so used to c++, i need my void functions!) But it is having a runtime error '1004' "Application-defined or object-defined error.
and it cites this part of the code
CodeFunction FillRow(row As Integer) As Range If Len(Range("A" & CStr(row))) = 0 Then Set FillRow = Range("A" & CStr(row)) Else: Set FillRow = Range("A" & CStr(row)).End(xlToRight).Offset(0, 1).Value 'This line Errors End If End Function
By the way, why cant I simply use the Range("A16").End(x1ToRight).Offset(0,1) alone and without a separate function? will that not do the same thing?
-
Here is my current project, maybe it would help to see the error I am getting, Just run CreateSheets and that should run through all the subs on its own.
Thanks again
-
Thanks for the attachment...that cleared things up.
What was happening is that it was looking for the end of the range from "A15", but since there was nothing else in the row (From B15 on), The ".End" took it all the way to the last column in the page. Then you tried to "Offset" by one more column. The problem was happening because there wasn't 1 more column to offset to. The following revision to the FillRow function should fix this problem:
Code
Display MoreFunction FillRow(row As Integer) As Range If Len(Range("A" & CStr(row))) = 0 Then Set FillRow = Range("A" & CStr(row)) Else If Len(Range("A" & CStr(row)).Offset(0, 1)) = 0 Then Set FillRow = Range("A" & CStr(row)).Offset(0, 1) Else Set FillRow = Range("A" & CStr(row)).End(xlToRight).Offset(0, 1).Value End If End If End Function
-
-
Hmm It seemed to run a little further. I got a Run time error with that new function, of "Object required"
Debug is citing this line
But it did start to fill in some values on the first empty row where i wanted it to. But didnt get past the first line of data
-
This one's just going to fight with us all the way, huh... Try this new version instead. I've used similar to this before and am almost positive it should work:
Code
Display MoreFunction FillRow(startspot as Range) as range Line1: If startspot = "" Then Set FillRow = startspot Goto Line2 Else Set startspot = startspot.Offset(0,1) Goto Line1 End If Line2: End Function
Hopeful -
jmhans -
-
FillRow(Range("A15"))
Or, it can be modified if passing a row only as an int is easier
If you want to do that, just change "startspot as Range" to "rowno as integer" and add these lines at the beginning of the function (before Line1:) saying:
Dim startspot as range
Set startspot = Range("A" & cstr(rowno))Then it should work.
-
Incredible!
jmhans It works BEAUTIFULLY! Thank you so much!
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!