Posts by firefiend

    Re: Auto-formatting Rows With iLastRow Variable

    RoyUK, you are the man!

    A fine lass named Kristy shared this snippet with me:

    I hate loops. They do wonderful things but always cause me problems; so I prefer loopless.

    But, the best tool in coding anything is "option."

    Thanks again, RoyUK.


    okay, I think I have but one more major coding obstacle for this project I'm assigned to.

    I need each row in the Range("A2:G" & iLastRow) to be formatted with the top and bottom border.

    I'd prefer to go loopless but if that's not possible I will use the loop.

    Anyone have experience with this kind of thing?


    Re: Assigning A Formula To A Variable

    hmm, interesting. I already thought about the messege box approach but this macro is part of a larger macro I wouldn't know how many rows were actually needed when the input box appeared.

    I found a working solution with:

    rowRange = ActiveSheet.Cells.Rows.Count - Application.CountBlank(Range("g:g")) + 1

    but your code seems much cleaner. What are the practical differences?

    I'm gonna play around with your suggests so I know how they work and what not and then decide on which is best. Coming from a X(HTML)/PhP background, your leaner code will probably win out, lol.

    thanks for the help,


    G:G contains a list of integars, though some cells are blank; lets say 75 of the 100 cells in data range are < 0.

    I want a macro which copies a range where the number of rows = the number of values in G:G.

    my Macro:

    sub myMacro()
    Dim rowRange As Integer
    rowRange = Count("G:G")
    End Sub

    This, as you guessed, comes back with an error. Sub or Function not defined.

    Anyone the proper syntax for assigning a formula to a variable?

    Re: Converting Numbers Stored As Text To Numbers Via Macro

    Quote from royUK

    It would be in the spirit of sharing knowledge to post your solution!

    But of course.

    Sub macro()
    Range("F:F").Select 'specify the range which suits your purpose
    With Selection
    Selection.NumberFormat = "General"
    .Value = .Value
    End With
    End Sub

    This macro will take the range and convert those pesky string numbers to numerical data.

    I have a spreadsheet that is sourced from a Database and I need to extract certain data from it and make it "pretty."

    the tables are attached as a .png 'cause I can't enter the html and make it render.

    but basically, I need either a function or some other snippet of code that will examine each row, determine the data in the B:B and D:D columns and output it accordingly.

    I've tried the simple IF(AND()) functions but it only seems to look at the first row despite the range.

    Anyone with more VBA experience have any ideas?