Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

VBA Question for Excel

  • 09-04-2015 03:26PM
    #1
    RebelRebel
    Registered Users, Registered Users 2 Posts: 127 ✭✭


    I have VBA Excel question I’m hoping someone can help me with.

    I have a spreadsheet with two worksheets. One called Data and a second called Computations.

    And a very simplified version of what I want to do is as follows: I want to create a macro to check Cell A1 in the Data worksheetis equal to 1 (for example ) and if so insert 1 in cell A1 of the Computations worksheet, otherwise insert 2. So the formual would be very simply =if(Data!A1=1,1,2)

    I then want this to continue down the rest of column A until the cells in Column A of the Data worksheet are blank. So I guess I want this to loop until there is no further information to process. And then I want to move on to Column B which would have a different formula.

    I can do this by recording a macro and dragging the formula down etc. but I figure there must be a simpler way to code it.

    This is just a simple version of what I want to do but if someone can enlighten me on the code I could for this then I thinik I’ll be able to adapt the code to my needs.

    Any help greatly appreciated.


Comments

  • #2
    TrueDub
    Moderators, Sports Moderators, Regional Abroad Moderators, Paid Member Posts: 2,691 Mod ✭✭✭✭TrueDub


    What you need to do is define a Range of cells, covering the cells you want to process, then loop through then using a ForEach or similar to do the processing you want.


  • #3
    croo
    Moderators, Technology & Internet Moderators Posts: 1,338 Mod ✭✭✭✭croo


    RebelRebel wrote: »
    I can do this by recording a macro and dragging the formula down etc. but I figure there must be a simpler way to code it.
    Do that, then examine the macro code created and it will show you exactly how to do it!


  • #4
    Thoie
    Registered Users, Registered Users 2 Posts: 6,340 ✭✭✭Thoie


    This may be of some help
    intNoOfRows = ActiveSheet.UsedRange.Rows.Count          ' Figures out the last row

    Set a variable (intNoOfRows) to find the last used row - you can use that in your looping.

    strCopyRange = "B18:B" & intNoOfRows                    ' Makes a string "B18:#" (excludes header)

    This is a string that I can use where ever I'd use a range. So in this case if the last used row is 27, for example, this gives me a string "B18:B27". I can then use that variable (strCopyRange) in a range command. For example
    Selection.AutoFill Destination:=Range(strCopyRange)     ' This is filling the Range("B18:B<end>").Select down to the last row

    Does that help get you started?


Advertisement