Page 2 of 2 FirstFirst 1 2
Results 11 to 17 of 17

Thread: using a macro to put formulas in cells.

  1. #11
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,663
    Articles
    0
    Excel Version
    O365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by Simi View Post
    And I really like the easier way to use the quotes Bob, that makes looking at the code much easier than trying to remember what chr(34) is.
    Many disagree with me, but I think so too. In some situations, it takes a bit of trial and error to get the right number, I never remember fully the rules, but I can live with that.

  2. #12
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Ironically, it never actually occured to me to use Chr(34) for putting in quotes. I've also doubled up to get them. Works with other languages like SQL too, so it can't be all bad.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #13
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,663
    Articles
    0
    Excel Version
    O365
    I started out by always using Chr(34), but switched to embedded quotes as I just didn't like it. If you don't like embedded quotes, a better way in my view is to define a quote constant and use that.

    Code:
    Const quote As String = """"Worksheets("Totals").Cells(cellCount + 10, 5).Formula = _
        "=COUNTIF('" & cellCount & "'!G:G," & quote & "H" & quote & ")"
    Obvious and meaningful. This was my interim stage until I went fully embedded.

  4. #14
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Before I knew any better, I got very familiar with the Chr() function. You see I didn't know there was a very simple way to refer to cells by number and thought it had to be done by letter.

    This is the code I was using to find the last column on a sheet I had, it makes me cringe to look at now. Also it is only good from A to ZZ.

    Code:
    'reset variables to figure how many columns there are for sorting
          colCnt = 75 ' start on col K
          colCnt2 = 64
          colNameMax = Chr(colCnt)
          
          
          'count how many columns to sort
          Do While IsEmpty(Worksheets(sheetName(sheetCnt)).Range(colNameMax & "1")) = False
             Worksheets(sheetName(sheetCnt)).Range(colNameMax & "3").Formula = "=sum(" & colNameMax & "9:" & colNameMax & totalRowCnt(sheetCnt) & ")"
             'colCnt = colCnt + 1
             If colCnt = 90 Then
                colCnt = 65
                colCnt2 = colCnt2 + 1
                colNameMax = Chr(colCnt2) & Chr(colCnt)
             ElseIf colCnt2 > 64 Then
                'colCnt2 = colCnt2 + 1
                colCnt = colCnt + 1
                colNameMax = Chr(colCnt2) & Chr(colCnt)
             Else
                colCnt = colCnt + 1
                colNameMax = Chr(colCnt)
             End If
          Loop
             
          'need to -1 from the column count to have accurate count
          If colCnt = 65 And colCnt2 = 65 Then
             colCnt = 90
             colNameMax = Chr(colCnt)
          ElseIf colCnt = 65 And colCnt2 > 64 Then
             colCnt = 90
             colCnt2 = colCnt2 - 1
             colNameMax = Chr(colCnt2) & Chr(colCnt)
          ElseIf colCnt > 65 And colCnt < 91 And colCnt2 > 64 Then
             colCnt = colCnt - 1
             colNameMax = Chr(colCnt2) & Chr(colCnt)
          Else
             colCnt = colCnt - 1
             colNameMax = Chr(colCnt)
          End If
    oh how easier it is to do something like

    Code:
    lastCol = .Cells(14, .Columns.Count).End(xlToLeft).Column
    Well, just goes to show how much I have learned since finding this site
    Last edited by Simi; 2012-05-17 at 09:56 PM.

  5. #15
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    You know, Simi, if the code you wrote the month before always makes you cringe it will mean you're always learning.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #16
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,663
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by Ken Puls View Post
    You know, Simi, if the code you wrote the month before always makes you cringe it will mean you're always learning.
    If the code you wrote the month before DOESN'T make you cringe, you are incapable of improving!

  7. #17
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by bob phillips View Post
    if the code you wrote the month before doesn't make you cringe, you are incapable of improving!
    lol!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 2 of 2 FirstFirst 1 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •