Posts by Foxxy1

I simply want to make a selection, then run code to color every other row grey (within the selection only, not beyond). I know there is a way to do this through conditional formating. I can use this as I have already used all 3 conditions. Any help is appreaciated. Thank you.

Re: Find contents of a cell throughout the entire work book

Thanks for the response. A couple things though. First I'm getting a compile error stating that the method is not found (refering to ".Find" in the 10th line of code). Second, as I'm reviewing the code it looks like you are displaying the result in a message box. I'm hoping to get the information filled out in two columns. There will also be several listings for each one. I should have mentioned that earlier but I didn't think about it. Sorry.

I have my time card setup where each week is on a different tab. Each tab is named the date that I'm turning it in, every wednesday. I want to search through my entire file to discover the contents of the cells directly below for two rows. The value that I'm searching for will the job number, below that is the # code that tells me what I was doing on it, and below that is how much time I spent on it. Any suggestions are greatly appreciated. Thank you.

Re: Stop looping code

num1 = Sheets("List").Range("D2").End(xlDown).Row 
Range("M1").FormulaR1C1 = "=IF(PDs!R2C2="""",List!R[1]C[-8],IF(PDs!R2C2=List!R[1]C[-9],List!R[1]C[-8],12345))" 
Range("M1", Cells(num1, 13)).Formula = Range("M1").Formula

Well, I am going to want the first line to be referencing a file called "Pricing", which will be opened automatically when this file is opened. In the second I'm going to want the equation to end up in another file, my "Personal" file that will also be open, on a sheet called "TempLists". I guess for that I just need to select that workbook before I start the formula, but I'm not sure how to go about that. Thanks again Andy!

Re: Stop looping code

You know, I'm starting to modify this a little bit. My lists are really going to be in 2 seperate files. Is there anything like a workbook.select. Also with the code as follows how it be set up to reference another sheet?

num1 = Sheets("List").Range("D2").End(xlDown).Row
                Range("M1").FormulaR1C1 = "=IF(PDs!R2C2="""",List!R[1]C[-8],IF(PDs!R2C2=List!R[1]C[-9],List!R[1]C[-8],12345))"
                Range("M1", Cells(num1, 13)).Formula = Range("M1").Formula

Any help is appreaciated. Thanks!

Re: printers list

Cant you just create the list yourself and reference the area that you have it. Is this going to be a variable list that you want to work on anyones computer? Perhaps a little more information might help.

Re: Stop looping code

Thanks Andy that worked exactly the way that I need it to.

Okay, I have a worksheet that, I'll be honest, has some code that's probably not the best it could be, but it works. I may try and streamline the code later on, but we'll see if I ever have the time before it needs to be used. The point is my code works, with one minor glitch that doesn't bug or anything, it's just hassle waiting for the loop to time out. When cells a2 &/or b2, contain data, along with c2 containing data, and then the values of a2 & b2 or deleted, the code loops. I know why sort of, but I'm not sure how to discontinue the code, or if there is a way to set the code to run one time per cell change maybe. Any other hints and help would be appreciated as far as cleaning up the construction of the code(preferably with examples, as you'll see in the code, I'm a bit of a hack, not so much by choice, but through lack of real training). Thanks everyone.

Re: Count the number of months in between dates

That formula can actually be placed in whatever cell you would like it to be placed in. I've attached an example sheet showing this.

Looks like you beat me to the punch pradeep atm.lol

Re: Listbox, 2 worksheets

Hi YeJiNaRa,

Welcme to Ozgrid. I think what you might be looking for is the vlookup function. If you post an example of you worksheet though, we might be able to help you better.

Re: Create pull down lists dependent on eachother

I guess what I'm saying is that I don't know if or how, within the code for autofilter, to reference a certain cell value. I know how to set the code to filter by certain pre-difined text, but not the variable of what a cell value contains. Is this possible? As far as what you mentioned about the user filtering, they aren't going to, other than by putting a vaule in that certain cell that I would want be referenced in the code. Hope all this makes sense.

Combobox Linked Cell

I am trying to run an if/then/else in my code. The code is an is essentially:

if C2 is blank, do this, if not do this.

The problem is C2 is the linked cell to a combobox, the code runs when the combobox loses the focus. It appears to me that the value for C2 is not being changed before the code is run. The link below is a little more background on the problem with an example.

edit: Please stick to the same thread - I have merged them. Will

Re: If then else

Okay, I think that the whole linked cell thing is the problem, I just looked at the code again with fresh eyes and decided I could test it by telling the code to look at F1 instead of C1 to look for the "" value. I then manually changed the value back and forth between nothing and something just to see that when the code ran after the box lost focus it was moving to a2 or b2, and it was. So this must be the problem. I'm not sure if or how I can make the vaule for the linked cell get placed before the code runs. Any sugesstions? Should I start this as a new thread?

Re: If then else

Well, i need the 3 combos to have lists that when a selection is made, the other boxes update accordingly. For instance, if in the 3rd box I choose Civic, the other 2 boxes would update to show, Honda, and 2 or 4 door compact. Those boxes though, would still have a full list of choices, in case it is not what they want. Now, say all are blank, and I instead choose from the first box, Dodge, the other 2 boxes need lists that correspond to dodge, which would be: SUV, Muscle Car, Full-size Pickup, & Super Car for the 2nd box, and the 3rd would contain, Durango, Viper, Ram, & Charger. Once a selection is choosen in the 2nd box, I want the lists to update accordingly also. Now, I also want to be able to choose from the 2nd box first also, which would then limit all the other lists.

I wonder if the problem has something to do with the linked cell, it seems like the cell should update before the macro runs, but I wonder if it's not.

Re: If then else

Essential what I'm trying to do is make sure that when someone looks at the list for the 3rd combobox, it runs certain code. If the decide to select nothing, I want pretty much nothing to happen, but if they do select a choice, I need the code to run what you can see in the else portion of the statement.

Re: If then else

Private Sub ComboBox3_LostFocus()
    If Range("C1") = "" Or Range("C1") = 0 Then
       Range("A2").Select
    Else
        Sheets("Combos").Select
        Range("K1").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(Combos!R3C1,List!C3:C5,3,FALSE)"
        Range("L1").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(Combos!R3C1,List!C3:C5,2,FALSE)"
        Range("K1").Copy
        Range("A1").Select
        ActiveSheet.Paste
        Range("L1").Copy
        Range("C1").Select
        ActiveSheet.Paste
        Columns("K:L").ClearContents
        ComboBox1.ListFillRange = "ManuList"
        ComboBox2.ListFillRange = "DescList"
        Range("B2").Select
    End If
End Sub

Well this is the code that I have altogether, but it doesn't quite do what I need. It seems to look at C1 as not having a value even when it does. I've attached an example so you don't have to build anything.

I'm just trying to figure out what the code would look like if certain conditions were met.

Bsaically I want the code to say if "A1" is blank or equals nothing, then select "A2". If "A1 is not blank or is not equal to nothing, then go to "B1". Hope this make sense. Thanks :thanx: .

Re: Code is a circular ref???

You know I tried changing the part

Private Sub ComboBox1_Change()

to

Private Sub ComboBox1_LostFocus()

and this seems to work fine.

Thanks for your help!

Re: Code is a circular ref???

What I'm trying to accomplish is when I choose a manufacturer from the list in the combo box, I want to create a list of descriptions (i.e. roadster,coupe,sedan) that will become the list for another combobox. When nothing is chosen, however, I want the full list of descriptions to be there. You'll notice in the example, on the sheet named List, Dodge only has SUV, Super Car, Full-size Pickup, and Muscle Car as possible descriptions associated with it. So, if Dodge was chosen in the combo box, those four choices would compile the list for a different combo box.

I'm not really sure what is happeing. I'm trying to build a list in column g using formulas, then I only want certain answers left. It seems that code is circular for some reason. I thought it might be due to a linked cell to a combobox but I changed the link to a cell on a different sheet and it still caused problems. My Excel keeps blowing up everytime I try to run it. I've attached an example. Try changing the value in the combobox and see what happens.