How to work with ranges in IF ELSE formula in VBA? I found for loops but they are erroring. Any help would be appreciated. If you could help me print a range, that would be helpful to understand and use!!

like
Posting as :
works at
You are currently posting as works at

Can you give some more details of what you're trying to do or examples of the code that's erroring? A For or While Loop may be the easiest way, depending what you're trying to do.

like

I’m essentially trying to run a range of numbers (column T) through a FOR loop and then multiple IF statements to find out if that’s compliant or not based on the State rules. Does that help or I can elaborate further

like

Also if you're trying to apply different rules per state, you might have a better time using a CASE statement rather than a series of IF/ELSE blocks.

For r=2 to lastrow
Select Case Range("T" & r)
Case "CA"
Debug.Print "California stuff"
Case "CO"
Debug.Print "Colorado stuff"
...
Case Else
Debug.Print "No State"
End Select
Next r

like

Make sure the starting point (row 1 in this example) has data and then “for mylooprange = 1 to activesheet.usedrange.rows.count”, “next mylooprange”.

like

Thanks Everyone!! It’s working :)

Related Posts

How do you get out of the burnout cycle? I’ve felt overwhelmed for a while and now I’m in the “can’t function at all” stage. It’s so hard explaining this to my manager because I already have a workload that’s lighter than others (not because of performance or my request). Any tips on how to get re-engaged, since the overwhelming feeling will come back?

like

Jobs opportunities at Indium Software

Post Photo
like

What happens if you got team matched but then got rejected by HC. Am I going to put back into team matching for other roles? Or is this the end?

like

And with that mouse click, the 6 figure student loans are paid off. F you Fedloan, and all of your BS over the past 3 years. Freedom!

likeupliftinghelpfulfunnysmart

Would you take a job for less salary if it was a less toxic environment than your current situation?

like

Currently have the FRM title.
I failed CFA level 3 once.

I'm hoping to eventually do CAIA, but only after I'm done with CFA. I'm also hoping to do non-financial certifications, like Certified analytics professional (CAP), and learn about web development.

While I may have the title of manager, I am still an individual contributor. The type of work I do involves quantitative analysis/data analysis/science.

Any suggestions as to what I should start with?

like

James Conner, Adrian Peterson, or frank gore? Other RB is James Robinson. Full PPR league.

like

Thoughts on Deloittes workforce transformation and org design practice? Considering transferring and just want to get an idea of WLB.

likesmart

Anyone work at Velocity Global or know anything about the product and company culture? Largest international PEO, founded 2014, hundreds of employees.

like

I’ve been laying in bed all day hoping the guy I like will text to hang out but nope. We did hang out last night and supposed to hike Monday but I want to hang out today and tomorrow but figured it may be excessive 😫. On the bright side I finished Stranger things season 4

like

Should I wfh first day on vyvance? I just don’t know how I’ll react

like

Is it safe to join a 30-40 people startup and leaving a big giant? What are the pros and cons?

like
like

What’s your practice’s ratio of males to females in advisory?

Female here and interviewing for a handful of accounting advisory roles but I’ve only interviewed with old white males. Is this a male dominated field?

likehelpful

Happy Friday Fishes. I am new to this app.
Please like this post. So that i have enough 💙 to enable dm.

like

Hi fishes,

I'm planning to switch to a new role in finance and banking or sales. Your refrerals and suggestions are most welocmed.

like

What is the salary range of Strategy - Associate Manager in EYP? Round about estimate would be helpful.

like

Do we have flexible travel?

Anyone work at Meta? How is the culture in the legal department? Is it cut throat and competitive or collaborative and inclusive? Considering some opportunities there

likehelpful

Tried buy side bowl but this may be better. Trying to explore institutional sales roles but no idea how these groups are structured or how to break into them. Currently working in buy side HY investing with 3 YOE

like

Additional Posts in Excel Genius

What are your top Excel ninja tricks? Ones that few people know...

like

how do you index or vlookup when the match isn't exact? like one cell has a comma or space after the value...?

like

Google Sheets vs Excel - pros? cons? differences?

likefunnysmarthelpful

Hi! I have knowledge of pivot tables, vlook ups. But I'm rusty on if statements and would like more knowledge on excel formulas. I'm looking for excel classes to take online. Any suggestions?

like

Any recommend courses (coursera, edx, etc.) people would recommend for user with intermediate experience with excel?

like

What are the most typical models you all build

likehelpful

What's the F4 analogy in Office 365?

like

Ok, using excel is not a primary function for my role (this will be very obvious shortly...) So please be kind! On my sheet, in the first and third columns, I have lists of reference numbers. In the second column I have a client name. I already identified the duplicates in the first and third columns using conditional formatting.What I need to find now is column 2 value, whenever value in column 3 is identified as duplicate. Problem is, I have over 120k rows of data so I can't filter! Help!

like

Has anyone encountered this error before? We can’t access a workpaper with 70% of the work done 😩😩

Post Photo
like

Excel Fact Of The Day:

An apostrophe before a cell value forces Excel to interpret the value as text. (Continued on next post).

like

A lot of the work at my new employer is quite redundant and can be automated via macros. What are some of the best resources to learn vba?

like

At my old firms when I would be using excel and modeling using the font schemes (blue, green, black for hardcode, link, and formula) I could use the “control shift ;” to shift through the schemes. My new firm doesn’t do this. Does anyone know why the shortcut would be different and how I can add it back?

like

How do you all prepare for excel/financial modeling tests? Every firm is different, so I imagine there are multiple effective ways to get ready.

likehelpful

Need some help. Is there a fax function (Filter) so that if I want my subtotals to show a number but nothing below it to report. One expansion of a row the subtotals show but no numbers below it.

like

Why when I refresh a query, the table where the query is loaded to just gets duplicate rows? Same with the pivot table that feeds off of that query?

like

Our system is really lacking and we do a lot of work outside of it to track data. Currently I’m using Excel to track data from multiple resources, however, it’s so manual and prone to error. Any suggestions to keep the data clean when I have to pull info from multiple places? I’m constantly auditing and we won’t even look at getting a new system until next year :(

Is there a way to simultaneously merge spreadsheets (or workbooks) and decide which columns/rows to update without a third party tool?

like

Can I write a macro that will fill out a table of information based on cells that have comments on them? For example, if G12 has a comment, I want to pull text from that comment + data in A12.

like

Last cell question. Some of my simple models are massive in file size because every tab has a last cell of ~XFD65. But I’m not using anything past ~U100. Continued.

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal