inserting formula in a range that increments based on user input
Posted by Roger on July 30, 2001 4:21 PM
Hi all,
It's my first post here so forgive my stupidity. First for all the details.
1) using excel 97 (is that laughter I hear? stupid company wont upgrade their software :-( )
2)I have a workbook that is used for collecting test data. It auto numbers all sheets in the workbook with the variables start and finish.
3)on each sheet I would like to have vba place the formula in the cells of a certain column based on start to finish.
4)the formula increments by one
5) as of right now I have a loop that enters a formula but it doesnt increment. and no I'm not using $ signs in the formula.
6)also is there a way if the out put of a formula is #div/0 to display this just as 0 or a blank cell? Cause untill I enter the test data the cells will say div/0
7) then based on the output of the formula I need to conditionally format that range of cells with an upper and lower limit entered in textboxes by the user....
Ok so there is the challenge...Its kicked the crap out of me and the only other guy who knows anything about vb in excel. I've searched the net and haven't found an answer yet. Any help would be greatly appericated.
Roger
Posted by Cory on July 31, 2001 7:18 AM
I'm not sure if I can understand your description but I'm going to take a stab at it... First, from what I can gather from your information, you have starting data in column A (say, A1) and finishing data in column B (B1). You have a macro that loops through column C one cell at a time and enters a formula. Your problem is this formula doesn't increment itself by one as it goes down the column. To fix that, change your macro so it only enters the first formula in C1, then add this code to it:
Range("B2").Select
Selection.End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
This should increment it by one (not to mention speed everything up because you're not using that loop..)
Then, to hide the DIV/0 errors, write your formulas something like this:
=If(IsError(A1-B1),"",A1-B1)
This says if A1-B1 causes an error (such as DIV/0) then put nothing ("") in the cell, else go ahead and do the calculation...
Last, about your conditional format based on user data entry: it can be done and not too difficultly but you'd have to be a bit more specific before I could help you. It should be some form of Select Case statement refering to the text boxes where the user enters the data. maybe something like:
Private Sub WhenToFormat()
Select Case Textbox1.Text
Case "a" 'if user enters an "a" then
With Worksheets(1).Range("A1").FormatConditions(1)
With .Borders 'changes the border style
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 6
End With
With .Font 'changes the font properties
.Bold = True
.ColorIndex = 3
End With
End With
Case "b" 'this is the second condition
Range("b1").Value = "Goodbye"
End Select
End Sub
I don't know if you can piece anything useful out of this, but I tried....
Hope it helped some
Cory
