Posts by firefiend

Re: Assigning A Formula To A Variable

ah, I see what you're saying. I'll play around with them and learn how they differ from each other in form and result.

Thanks for the help.

Re: Auto-formatting Rows With iLastRow Variable

RoyUK, you are the man!

A fine lass named Kristy shared this snippet with me:

With Range("A2:G" & iLastRow)
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
    End With
End With

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.

:music:

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?

~Len

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,

~Len

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 LionelNZ

O r you could use something lik ASAP utilities

what are these ASAP utilities you speak of? I'm not familiar, haha.

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.

Re: Converting Numbers Stored As Text To Numbers Via Macro

needs to be a macro but thanks.

I found a resource for the answer.

I'm looking for the VBA command for this function. I tried just recording a macro in where I perform the task but it didn't record anything. Tried it several times even.

Anyone have any ideas about this?

Re: Reconstructing Exported Database Data

Is there anyone that can point me in the right direction here?

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?