Help a newcomer to VBA. if statement or equivalent

[TreeSqueak]

I have an excel document that I get every month.

I would usually type the formula =IF(Q3="I","Colonial FS Insurance","Colonial 1st State") into cell AB3 then double click the corner to drag down to the last row.

The number of rows changes every month and can be anything from 30 to over 2000 rows.

I was going to choose an arbitrary high number and have it drag down to that row using [[Selection.AutoFill Destination:=Range("AB3:AB3000"), Type:=xlFillDefault]]

but VBA wont let me put the formula in the cell in the first place; [[Range("AT11").formula = "=IF(Q3="I","Colonial FS Insurance","Colonial 1st State")"]] gets errors because of the internal " symbols.

Is there a simple fix?
Ive tried a few other methods but I cant get it to work, for example I tried having collumn AB equal Collumn Q, then turning it into values then using find and replace, but then all the letter I's get replaced rather than just the cells that only equal I.

'REPLACE I WITH INSURANCE IN COLLUMN ab Columns("AB").Replace What:="I", _ Replacement:="INSURANCE", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False 'REPLACE A WITH CFS IN COLLUMN ab Columns("AB").Replace What:="A", _ Replacement:="CFS", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False

<tbody>
</tbody>

I hope this makes sense.

Ive attached a fake sheet in the same format as mine. all the "11" would be numbers or text. only collumn Q is important. the data starts in row 3

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	I	11	11	11	11	11	11	11	11	11	11
11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	I	11	11	11	11	11	11	11	11	11	11
11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	I	11	11	11	11	11	11	11	11	11	11
11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	A	11	11	11	11	11	11	11	11	11	11
11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	A	11	11	11	11	11	11	11	11	11	11
11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	S	11	11	11	11	11	11	11	11	11	11
11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	A	11	11	11	11	11	11	11	11	11	11
11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	I	11	11	11	11	11	11	11	11	11	11
11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	11	I	11	11	11	11	11	11	11	11	11	11

<tbody>
</tbody>

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub TreeSqueak()
   Dim UsdRws As Long
   UsdRws = Range("Q" & Rows.Count).End(xlUp).Row
   Range("AB3:AB" & UsdRws).Formula = "=IF(Q3=""I"",""Colonial FS Insurance"",""Colonial 1st State"")"
End Sub

 

[TreeSqueak]

WOW. that works perfectly. I can't believe it was as simple as doubling up on the " marks. AND you automated it to go down to the last row.
Thankyou sooo much. I imagine I will be using this for a long time. as well as using parts of it, like the Usdrws line.

In the interest of me understanding things and not just copying code.

 UsdRws = Range("Q" & Rows.Count).End(xlUp).Row

This line looks to me like it looks at collumn Q and counts how many have values to return a number you can later use in a range. but I'm not 100% sure what the

.End(xlUp).Row

section does.

does the

.Row

mean that the number found by the rest is a row number thereby allowing it to be used in a range?

I have no Idea on what the

.End(xlUp)

means.

I can and will google this, but would love an explanation with context if you have time.

Please and thankyou so much.

 

[TreeSqueak]

Ok, so on googling the code.

 .End(xlUp).Row

looks like its used as a single statement meaning something like; "until the last row".

This means that the [.Count] stops counting on the last row.

Coding is so logical. I wish I had taken classes at school, or had the time to do so now. I love it. <3

 

[Fluff]

The part in red

UsdRws = [COLOR=#ff0000]Range("Q" & Rows.Count)[/COLOR][COLOR=#0000ff].End(xlUp)[/COLOR][COLOR=#ff8c00].Row[/COLOR]

looks at the very last row in col Q (ie Q1048576 with .xls? files) and the part in blue then moves up the column until it finds a non-empty cell. Lastly the part in orange returns the row number for the cell it ends up on.
So all in all that line stores the row number for the last used row in the column.
HTH

 

[TreeSqueak]

Very well explained Thank You.
And I'm learning something I find really interesting.
I've brought the VBA for dummies ebook. I'm going to try to get good enough to be someone answering the threads

 

[Fluff]

You're welcome & thanks for the feedback.