ExcelBanter - Autofill & Lookup Function

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Autofill & Lookup Function (https://www.excelbanter.com/excel-programming/297047-autofill-lookup-function.html)

Chris Hankin

Autofill & Lookup Function

Hello,

Could someone please help me with the following:

I have started creating an Excel spreadsheet. In column A is a list of
registration numbers. There are many blank cells in column A underneath
each registration number. The number of blank cells underneath each
registration number varies. Sometimes there may be 2 blank cells or 30
blank cells or no blank cells. I have managed to get Excel to locate the
LastCell which has a registration number in it. I used the following
formula for LastCell:

=OFFSET('SA REGISTER'!$A$2,COUNTA('SA REGISTER'!$A$2:$A$20000)-1,0)

I used the following formula for LastCellStockcode:

=OFFSET('SA REGISTER'!$E$2,COUNTA('SA REGISTER'!$E$2:$E$20000)-1,0)

I then used the following macro to get Excel to locate the last registration
number in column A:

Sub Rego_No_Fill()
Range("A2").Select
Application.ScreenUpdating = False
Range("LastCellStockcode").Select
ActiveCell.Offset(0, -4).Range("A1").Select
Range("LastCell").Select
End Sub

So what I would like Excel to do is:

AutoFill from the LastCell (in column A) which has a registration number in
it to the end of the table. To find the end of the table, I use the formula
shown above:

=OFFSET('SA REGISTER'!$E$2,COUNTA('SA REGISTER'!$E$2:$E$20000)-1,0)

For example, if I run the above-mentioned macro, it will locate the last
registration number in my spreadsheet (in column A) and that happens to be
registration number: 556. There are 2 blank cells underneath registration
number 556. I would like Excel to AutoFill those 2 blank cells with the
same registration number of: 556. Thus, all 3 cells in column A will have
the registration number of: 556.

Any help would be greatly appreciated,

Kind regards,

Chris.

Tom Ogilvy

Autofill & Lookup Function

As best I can make out, this should do it. Use column E to find the last
cell and use column A to find the last registration number.

Dim rng as Range, rng1 as Range
set rng = Cells(rows.count,1).End(xlup)
set rng1 = Cells(rows.count,5).End(xlup)
Range(rng,rng1.offset(0,-4)).Value = rng.Value

--
Regards,
Tom Ogilvy

"Chris Hankin" wrote in message
...
Hello,

Could someone please help me with the following:

I have started creating an Excel spreadsheet. In column A is a list of
registration numbers. There are many blank cells in column A underneath
each registration number. The number of blank cells underneath each
registration number varies. Sometimes there may be 2 blank cells or 30
blank cells or no blank cells. I have managed to get Excel to locate the
LastCell which has a registration number in it. I used the following
formula for LastCell:

=OFFSET('SA REGISTER'!$A$2,COUNTA('SA REGISTER'!$A$2:$A$20000)-1,0)

I used the following formula for LastCellStockcode:

=OFFSET('SA REGISTER'!$E$2,COUNTA('SA REGISTER'!$E$2:$E$20000)-1,0)

I then used the following macro to get Excel to locate the last
registration
number in column A:

Sub Rego_No_Fill()
Range("A2").Select
Application.ScreenUpdating = False
Range("LastCellStockcode").Select
ActiveCell.Offset(0, -4).Range("A1").Select
Range("LastCell").Select
End Sub

So what I would like Excel to do is:

AutoFill from the LastCell (in column A) which has a registration number
in
it to the end of the table. To find the end of the table, I use the
formula
shown above:

=OFFSET('SA REGISTER'!$E$2,COUNTA('SA REGISTER'!$E$2:$E$20000)-1,0)

For example, if I run the above-mentioned macro, it will locate the last
registration number in my spreadsheet (in column A) and that happens to be
registration number: 556. There are 2 blank cells underneath registration
number 556. I would like Excel to AutoFill those 2 blank cells with the
same registration number of: 556. Thus, all 3 cells in column A will have
the registration number of: 556.

Any help would be greatly appreciated,

Kind regards,

Chris.

yogendra joshi

Autofill & Lookup Function

If you want to fill the registration number of the appropriate person
in the blanks, its really really simple... without using macro or named
ranges.

If you have entered range from A2 to A500, then select them and
Do this...
1. Press F5 (go to)
2. Select blanks and press ok
3. now your cells having blanks will be selected with active cell as A3
4. enter formula =A2 and press Ctrl + Enter the formula will be entered
in all the cells.
5. Now again select the entire range A2 : A500 and copy
6. Paste Special - Values...

It works, and works really well....

Hope this helps you.

I will try to find a cool link for this i read long time back...
till then this should help :)

Thanks, Yogendra

Chris Hankin wrote:
Hello,

Could someone please help me with the following:

I have started creating an Excel spreadsheet. In column A is a list of
registration numbers. There are many blank cells in column A underneath
each registration number. The number of blank cells underneath each
registration number varies. Sometimes there may be 2 blank cells or 30
blank cells or no blank cells. I have managed to get Excel to locate the
LastCell which has a registration number in it. I used the following
formula for LastCell:

=OFFSET('SA REGISTER'!$A$2,COUNTA('SA REGISTER'!$A$2:$A$20000)-1,0)

I used the following formula for LastCellStockcode:

=OFFSET('SA REGISTER'!$E$2,COUNTA('SA REGISTER'!$E$2:$E$20000)-1,0)

I then used the following macro to get Excel to locate the last registration
number in column A:

Sub Rego_No_Fill()
Range("A2").Select
Application.ScreenUpdating = False
Range("LastCellStockcode").Select
ActiveCell.Offset(0, -4).Range("A1").Select
Range("LastCell").Select
End Sub

So what I would like Excel to do is:

AutoFill from the LastCell (in column A) which has a registration number in
it to the end of the table. To find the end of the table, I use the formula
shown above:

=OFFSET('SA REGISTER'!$E$2,COUNTA('SA REGISTER'!$E$2:$E$20000)-1,0)

For example, if I run the above-mentioned macro, it will locate the last
registration number in my spreadsheet (in column A) and that happens to be
registration number: 556. There are 2 blank cells underneath registration
number 556. I would like Excel to AutoFill those 2 blank cells with the
same registration number of: 556. Thus, all 3 cells in column A will have
the registration number of: 556.

Any help would be greatly appreciated,

Kind regards,

Chris.

DSC[_15_]

Autofill & Lookup Function

Hi Chris

I used this code for a similar problem.
The Assumptions ive made are the your column E is filled to the Lastro
and that your data starts on row 2

Code
-------------------

Sub CopyDown()
'Macro for Copying Information down to blank cells
'From Data from Pivot Tables
'Written by DC on 15/01/2004

Dim dblLastRow As Double, dblRow1 As Double, dblrow2 As Double
Dim intMyRow As Integer, intCol As Integer, intStartRow As Integer
Dim strMyRange As String, StrMyText As String

intStartRow = 1
intCol = 1
dblLastRow = Range("E" & Rows.Count).End(xlUp).Row
dblrow2 = intStartRow

Do Until Cells(dblrow2, intCol).Row dblLastRow
dblRow1 = dblrow2
StrMyText = Cells(dblRow1, intCol).Formula

dblrow2 = Cells(dblRow1, intCol).End(xlDown).Offset(-1, 0).Row
If Cells(dblrow2, intCol).Formula = "" And dblrow2 <= dblLastRow Then
Range(Cells(dblRow1, intCol), Cells(dblrow2, intCol)).Formula = StrMyText
dblrow2 = dblrow2 + 1
ElseIf dblrow2 dblLastRow Then
dblrow2 = dblLastRow - 1
If Cells(dblrow2, intCol).Formula = "" Then
Range(Cells(dblRow1, intCol), Cells(dblrow2, intCol)).Formula = StrMyText
dblrow2 = dblrow2 - 1
End If
Exit Do
Else
dblrow2 = dblrow2 + 1
End If
Loop
End Sub

-------------------

HTH

Davi

--
Message posted from http://www.ExcelForum.com

yogendra joshi

Autofill & Lookup Function

You can also do this without selecting....

sub fill_last()
Range(Range("E65536").End(xlUp).Offset(0, -4), _
Range("A65536").End(xlUp)).Value = _
Range("A65536").End(xlUp).Value
End Sub

Tom Ogilvy wrote:

As best I can make out, this should do it. Use column E to find the last
cell and use column A to find the last registration number.

Dim rng as Range, rng1 as Range
set rng = Cells(rows.count,1).End(xlup)
set rng1 = Cells(rows.count,5).End(xlup)
Range(rng,rng1.offset(0,-4)).Value = rng.Value

yogendra joshi[_2_]

Autofill & Lookup Function

Tom Ogilvy

Autofill & Lookup Function

Please indicate where any selecting is going on????

If you mean without using variables, sure, but you then have to find the
same location twice.

--
Regards,
Tom Ogilvy

"yogendra joshi" wrote in message
...
You can also do this without selecting....

sub fill_last()
Range(Range("E65536").End(xlUp).Offset(0, -4), _
Range("A65536").End(xlUp)).Value = _
Range("A65536").End(xlUp).Value
End Sub

Tom Ogilvy wrote:

As best I can make out, this should do it. Use column E to find the last
cell and use column A to find the last registration number.

Dim rng as Range, rng1 as Range
set rng = Cells(rows.count,1).End(xlup)
set rng1 = Cells(rows.count,5).End(xlup)
Range(rng,rng1.offset(0,-4)).Value = rng.Value

yogendra joshi

Autofill & Lookup Function

Agreed...

I think i misread your code... sorry for messing up :)

Regards,

Yogendra

Tom Ogilvy wrote:

Please indicate where any selecting is going on????

If you mean without using variables, sure, but you then have to find the
same location twice.

yogendra joshi[_3_]

Autofill & Lookup Function

abstract of reply sent to Chris:

Hi Chris,

Do you want to do this in the entire column I mean:

A 1 2
2 3
B 1 2
2 3
C 1 2
2 3

My question is, are you interested in filling in only the last number,
or over the entire Column with respective cells?

If you want to do whatever i did using a macro, here is it:

Sub Macro1()
Intersect(ActiveSheet.UsedRange, Range("A:A")).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range(Range("A1"), Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:
_
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub

Hope this helps

Yogendr

--
Message posted from http://www.ExcelForum.com

Tom Ogilvy

Autofill & Lookup Function

4. enter formula =A2 and press Ctrl + Enter the formula will be
entered

I would add that this assumes A2 has a value and A3 is the first blank cell.
The more general direction is reference the cell above the active cell.
yogendra did state that A3 is assumed to be the activecell but I believe
specifically stating the above is clearer in understanding what is required.

as far as a reference, this technique has been posted in this group and
other Excel groups both as a manual and macro approach many times. So there
should be references in google groups. I believe the first time I saw it it
was posted by Myrna Larson several years ago.

--
Regards,
Tom Ogilvy

"yogendra joshi " wrote in
message ...
If you want to fill the registration number of the appropriate person
in the blanks, its really really simple... without using macro or named
ranges.

If you have entered range from A2 to A500, then select them and
Do this...
1. Press F5 (go to)
2. Select blanks and press ok
3. now your cells having blanks will be selected with active cell as
A3
4. enter formula =A2 and press Ctrl + Enter the formula will be
entered
in all the cells.
5. Now again select the entire range A2 : A500 and copy
6. Paste Special - Values...

It works, and works really well....

Hope this helps you.

I will try to find a cool link for this i read long time back...
till then this should help

Thanks, Yogendra

P.S. I had sent the reply by maill... but somehow it does not come here
:(

---
Message posted from http://www.ExcelForum.com/

Chris Hankin

Autofill & Lookup Function

Hi David,

Thankyou for your code.

Unfortunately, it didn't to what I expected it to do. That's my fault as I
didn't explain clearly what I needed.

I tried replying back to you but my replies get rejected by my mail-sever.

Thanks again,

Kind regards,

Chris.
"DSC " wrote in message
...
Hi Chris

I used this code for a similar problem.
The Assumptions ive made are the your column E is filled to the Lastrow
and that your data starts on row 2

Code:
--------------------

Sub CopyDown()
'Macro for Copying Information down to blank cells
'From Data from Pivot Tables
'Written by DC on 15/01/2004

Dim dblLastRow As Double, dblRow1 As Double, dblrow2 As Double
Dim intMyRow As Integer, intCol As Integer, intStartRow As Integer
Dim strMyRange As String, StrMyText As String

intStartRow = 1
intCol = 1
dblLastRow = Range("E" & Rows.Count).End(xlUp).Row
dblrow2 = intStartRow

Do Until Cells(dblrow2, intCol).Row dblLastRow
dblRow1 = dblrow2
StrMyText = Cells(dblRow1, intCol).Formula

dblrow2 = Cells(dblRow1, intCol).End(xlDown).Offset(-1, 0).Row
If Cells(dblrow2, intCol).Formula = "" And dblrow2 <= dblLastRow Then
Range(Cells(dblRow1, intCol), Cells(dblrow2, intCol)).Formula =
StrMyText
dblrow2 = dblrow2 + 1
ElseIf dblrow2 dblLastRow Then
dblrow2 = dblLastRow - 1
If Cells(dblrow2, intCol).Formula = "" Then
Range(Cells(dblRow1, intCol), Cells(dblrow2, intCol)).Formula =
StrMyText
dblrow2 = dblrow2 - 1
End If
Exit Do
Else
dblrow2 = dblrow2 + 1
End If
Loop
End Sub

--------------------

HTH

David

---
Message posted from http://www.ExcelForum.com/

Tom Ogilvy

Autofill & Lookup Function

Chris sent me a copy of his worksheet. The problem is that the cell below
the last registration number isn't seen as empty so any approach that used
End(xlup) or specialcells(xlBlanks) will fail. I provided him a macro that
loops through the appropriate range and finds the last cell with a
registration number and fills the appropriate area.

--
Regards,
Tom Ogilvy

"Chris Hankin" wrote in message
...
Hi David,

Thankyou for your code.

Unfortunately, it didn't to what I expected it to do. That's my fault as
I
didn't explain clearly what I needed.

I tried replying back to you but my replies get rejected by my mail-sever.

Thanks again,

Kind regards,

Chris.
"DSC " wrote in message
...
Hi Chris

I used this code for a similar problem.
The Assumptions ive made are the your column E is filled to the Lastrow
and that your data starts on row 2

Code:
--------------------

Sub CopyDown()
'Macro for Copying Information down to blank cells
'From Data from Pivot Tables
'Written by DC on 15/01/2004

Dim dblLastRow As Double, dblRow1 As Double, dblrow2 As Double
Dim intMyRow As Integer, intCol As Integer, intStartRow As Integer
Dim strMyRange As String, StrMyText As String

intStartRow = 1
intCol = 1
dblLastRow = Range("E" & Rows.Count).End(xlUp).Row
dblrow2 = intStartRow

Do Until Cells(dblrow2, intCol).Row dblLastRow
dblRow1 = dblrow2
StrMyText = Cells(dblRow1, intCol).Formula

dblrow2 = Cells(dblRow1, intCol).End(xlDown).Offset(-1, 0).Row
If Cells(dblrow2, intCol).Formula = "" And dblrow2 <= dblLastRow Then
Range(Cells(dblRow1, intCol), Cells(dblrow2, intCol)).Formula =
StrMyText
dblrow2 = dblrow2 + 1
ElseIf dblrow2 dblLastRow Then
dblrow2 = dblLastRow - 1
If Cells(dblrow2, intCol).Formula = "" Then
Range(Cells(dblRow1, intCol), Cells(dblrow2, intCol)).Formula =
StrMyText
dblrow2 = dblrow2 - 1
End If
Exit Do
Else
dblrow2 = dblrow2 + 1
End If
Loop
End Sub

--------------------

HTH

David

---
Message posted from http://www.ExcelForum.com/

All times are GMT +1. The time now is 01:21 AM.