Help a newcomer to VBA. if statement or equivalent

T

TreeSqueak

New Member
Joined
Sep 4, 2019
Messages
5
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.
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]'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[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]I[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]I[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]I[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]A[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]A[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]S[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]A[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]I[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]I[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about
Code: 
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
 
Upvote 0
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.
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
Code: 
.End(xlUp).Row
section does.

does the
Code: 
.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
Code: 
.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. :)
 
Upvote 0
Ok, so on googling the code.
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
 
Upvote 0
The part in red
Code: 
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
 
Upvote 0
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 :)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Replace a character string with other characters in column "K"
2
Replies
18
Views
475
harzer
H
R
  • Question Question
Is There a More Efficient Way to Code This?
Replies
2
Views
504
offthelip
O
R
  • Question Question
Picture Too Large? What picture!!
2
Replies
15
Views
1K
RandyD123
R
A
  • Question Question
VBA Highlight cell if contains specific text
Replies
4
Views
2K
DanteAmor
DanteAmor
L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
317
Linda Lo
L

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top