Find Cell Reference & Pass as end of range 1

[newestAF]

I'm not too versed in Excel VBA. I have a xls where I need to insert a column (E) and input formula "=right(D2,4)" and then copy that for all the rows that contain data. I do this each week and the number of rows changes. Below is what I have and been manually going in and changing the last number of the range. How can I automate it so I don't have to?

Sub Assignment_Tracker()
'
' Assignment_Tracker Macro
'
Rows("1:2").Select
Range("A2").Activate
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireColumn.AutoFit
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
ActiveCell.FormulaR1C1 = "Last_4"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1], 4)"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E343"), Type:=xlFillCopy
Range("E2:E343").Select
Range("A1").Select
End Sub

 

[lameid]

Record a new macro...

Select E3

Push ctrl+shift+end

Stop recording. I believe those recorded lines will give you what your after.

 

[newestAF]

That selects everything from that column down to the last row but also includes all columns right of selected start position.

 

[lameid]

Oops... I had another post in my head...

Use this key sequence instead...

ctrl+shift+[Down arrow]

 

[newestAF]

That chooses everything in that row. I did forget to mention that this spreadsheet (after editting) is used to append an access table. If the code is used in rows with no data, it causes an error in my dbs. This is why I need the formula to reset the last row by searching for the last row with data and only inputting the cell formula into rows with data.

 

[lameid]

Why not just import or link the data into access and then append the data to the appropriate table? I'm just saying it is easier in Access than Excel.

That said Ctrl+Shift+Down should give you the column... It will go wider if there is a merged cell that is wider. That is something like reason 422 that Excel should not be used to transmit data. That was hyperbole... there probably are only 200 reasons

You could also use CTRL+Down and then get the row property of the selection. I still say kill the Excel macro and use an Access query (there is only one formula).

 

[newestAF]

I have to login to another system that exports the data via xls. Before I can import the data into access, I have to tweek it to match with the tables. There is not an option in access (that I'm familiar with) that allows me to automatically input a column and trim another column to use the results as the data for new column. Not sure if that made sense...The reason for this trimming is for other actions within access and because it won't let me import without doing so.

 

[GlennUK]

Something like:

Range("E2:E" & Range("E2").End(xlDown).Row).FormulaR1C1 = "=RIGHT(RC[-1], 4)"

instead of using AutoFill.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

Oops, I mean:

Range("E2:E" & Range("F2").End(xlDown).Row).FormulaR1C1 = "=RIGHT(RC[-1], 4)"

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[newestAF]

GlennUK...You da man. Works like a charm. Thanks.

 

[lameid]

My Excel VBA is not as strong as I would like but Glenn's soluton looks good to me...

As for Access, say you imorted/linked the Excel data to a table named table1 and the field in question is field5...

Select Right([table1].[Field5],4) as NewField
From [table1]

Just paste the above into the SQL view of the query (making the necessary substitutions as I am sure your names will be more meaningful). That will give you a good place to start. Just modify it and then turn it into an append query, appending to the appropriate target table.

 

[SkipVought]

Hi,

I see statement like this...

I need to insert a column (E) and input formula "=right(D2,4)" and then copy that for all the rows that contain data. I do this each week and the number of rows changes.

and I think, "there's probably a better way."

First, if you have your table set up as a LIST, Data > List > Create..., all formulas will propogate to new rows as data is either entered or pasted in.

Second, regarding your inserted column. 1) Would you please explain the FUNCTIONAL reason for this insert? 2) What data is in row 1, Column E and following?

Please answer both questions and whatever other information may advance a solution. This may be able to be accomplished without any VBA at all.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lameid]

I know you have a working solution with VBA but I would listen to SkipVought... When I have an Excel problem, I hope he is the one that finds my post.

I think my solution is better than the Excel VBA solution and it sounds like SKip's list idea is even better than that.