Posts by jmhans

  • Custom Currency Format, Aligning Dollar Signs

    Hi all -


    This seems like it should be easy. Hopefully someone can help.


    I want to have a column of numbers formatted such that:


    1) Centered in the cell
    2) $ and thousands separator (,)
    3) No decimal places
    4) 0 is displayed as $ -


    Those are easy.


    Here's the sticky parts:


    4) I want the $ signs lined up regardless of size of the number (i.e. the number 99,999 would have 1 extra space between the $ and the first number compared to 100,000).
    5) I don't want to use the * because that pushes the dollar sign to the far left and the number to the far right - I want the numbers centered.


    So, basically, I want it to look like:


    $ 100,000,000
    $ 10,000,000
    $ 1,000,000


    etc...noting that I want it centered in the cell.

  • Change Control Sizes With Macro Permanently

    Hi -


    I have a userform with a lot of labels and text boxes (60+). When I originally made them, it appears they are a bit too small, so I need to change the height.


    The problem is they're stacked right on top of one another, so changing the height causes overlap, so after changing the height, I also need to change the "top" property. Of course, none of this is hard, I just want to automate it. I have the code to automate, and it works great, but only works at run-time. Is it possible to make these changes permanent without having to do it manually in design mode, and not need to have code like the following running in the initialize event every time?



    Thanks for any help you can give!

  • Flag Maximum Value

    Re: Find Max Without Filtering Data??


    Not sure if this is what you mean, but try doing the following (assumes you're data is in A2:A100 - you can extend accordingly):


    1) Select A2:A100


    2) Go to Format > Conditional Formatting


    3) Make the statement say:


    "Cell Value Is"..."equal to"..."=MAX(A$2:A$100)"


    4) Click the "Format" Button - and format however you want it to appear.


    5) Click OK and it should format the cell with the max value in it according to your specifications.


    You can Copy and Paste Special (Paste Formats) to any other columns you want the conditional format to appear, and it should work.


    Post back if this doesn't solve your question.

  • Tools & Format Options Cause Freeze

    Hi all -


    This is really strange. On one of my computers, Excel is freezing up just about any time I select something from the Tools or Format Menus (same goes if I use keyboard shortcuts to call any of those things). I have to kill it with the task manager.


    It is not a specific workbook, I've tried several, including new blank workbooks.


    It's not all menus, as File options seem to work. I also am able to Autosize columns by double clicking, but basically any other formatting things (i.e. CTRL + 1) do not work. I also tried to show the Cell Format dialog via VBA, (note: I am able to get to VBA editor, etc...), and it locked it up then too.


    Finally, I tried "Detect & Repair" from the Office disk, but that didn't do anything. Next, if necessary, I'll do a full re-install unless anybody else has heard of this and knows what the problem is.


    Word, PPT, etc... all seem to work perfectly.

  • 2 Scatterplots, One Chart, Different X

    Re: 2 Scatterplots, One Chart, Different X


    Just select the first series (both x and y values) and create the chart. Then, on the chart, right click and choose "Source Data". Then, on the series tab, Click "Add" and enter the ranges for the x and y of the second series (and name if desired).


    Hope that helps!

  • Return Result Based on Multiple Conditions/Criteria

    Re: If..then..else Vba Routine


    It seems like there's a better way...would:


    =INDEX('A'!K4:K11, MATCH("", 'A'!H4:H11, 0))


    Accomplish the desired result?


    If not - a function in VBA is pretty straightforward: it would look like:

    Code
    Function myFunction(LookupRng as Range, ReturnRng as Range) as Variant
ct = 1
Do Until LookupRng.Cells(ct,1) = ""
ct = ct + 1
Loop
myFunction = ReturnRng.cells(ct,1)
End Function


    Sorry, but that is untested, as I think the formula solution is the road to go down. If it doesn't work, hopefully the code is a good start.

  • Macro To Edit A Cell & Convert Formula To Text

    Re: Macro To Edit A Cell & Convert Formula To Text


    Press CTRL + ` (the same key as ~).


    A.K.A. Tools > Formula Auditing > Formula Auditing Mode


    This will display all formulas on the sheet as text.


    If you really want to just have one at a time, and if you want to use code, try:

    Code
    activecell.formula = "'" & activecell.formula


    HTH

  • Transpose Five Column Array To A Single Column

    Re: Transpose Five Column Array To A Single Column


    I think I have a solution, but it assumes the following:


    1) No data gaps:
    All cells in an upper row or further left column will be filled before any in a lower row or further right column (that is, if there's a value in C3, then there must have been values in A1:E1, B2:E2, and A3:B3)


    2) there will only be 5 columns and up to 1400 rows (this is easier to modify -but the provided formula conforms to these rules).



    Try the following formula in G1:


    Code
    =IF(CELL("row", F1)<=COUNT($A$1:$E$1400), INDEX($A$1:$E$1400,INT((CELL("row", F1)-1)/5)+1,MOD(CELL("row", F1)-1, 5)+1), "")


    Copy it down to G7000.


    If assumption 1 isn't true, maybe a macro approach is the way to go. Post back if that's the case.

  • Lottery Search Query

    Re: Lottery Search Query


    I think the countif still works - if the ticket has three of the four numbers, the function I wrote above will return 3. If it has 0, it will return 0, etc...
    (unless of course, order is important).


    Taking it a step further, you could then use autofilter where you filter for only those records that have 3 numbers


    Take a look at the attached:

  • Lottery Search Query

    Re: Lottery Search Query


    Well - I'm not sure exactly what you're asking, but here are my suggestions:


    1) As you said, split the numbers into separate columns -
    Select all of the data in the second column
    Go to "Data" > "Text to Columns"
    Choose "Delimited" and select "Comma" delimit type
    Follow through with the OK's and it should take care of the data split for you


    2) You'll probably just want to do a few "Countif()" statements. So, if ticket 1 is listed in range B2:E2, and the actual winning numbers are listed in B1:E1, then maybe in cell G2 you could put the formula: "=COUNTIF(B2:E2, B1) + COUNTIF(B2:E2, C1) +COUNTIF(B2:E2, D1)+COUNTIF(B2:E2, E1)"


    There may be a better formula (array type) if you have more than 4 numbers to look for, but I think this will suffice for the problem as stated.


    Let me know if you have questions.

  • Delete Text Rows With Criteria

    Re: Find Cells Without Numbers


    Are you saying that upon finding "1234-567-123-12" the row should be deleted, or if it doesn't find something like that, the row should be deleted?


    I'll assume that 1234-567-123-12 is a valid record, and therefore should not be deleted. If that's the case, try this code:


    Hope this helps!

  • Why are Formulas automatically overwritten by result?

    Re: Why are Formulas automatically overwritten by result?


    It sounds as though a "Copy...PasteSpecial(xlPasteValues)" is happening within the code. It may also be as subtle as

    Code
    Range("A1") = Range("A1")

    though too.


    (obviously, "A1" is a generalization in my example)...


    You said that it happens when macros are off though? That seems very strange.


    If possible, post up the code that causes the error to happen and maybe somebody can find the offending piece.

  • Variable not defined error

    Re: Variables


    Well - the error from the above code is just that "x" is not defined.


    Include:


    Code
    Dim x as integer


    somewhere above the For...Next Loop


    Then, I'd do:


    Code
    If Label1.Caption = "Spare" then
ComboBox10.ListIndex = 13
End if


    Where "Label1" is the label that is associated with ComboBox10.

  • Linking To Another Workbook In Macro

    Re: Linking To Another Workbook In Macro


    If the other workbook is open then just use something like:


    Code
    Workbooks("OtherWorkbookName.xls").sheets("SheetWhereDataIs").Range("RangeWhereDataIs")


    If it's not open, just use:


    Code
    workbooks.open ("FullFileName of other workbook")
'Then use code from above


    Take note of the "ThisWorkbook" codeword as well, as it will be useful whenever you want info from the workbook that the macro is housed in.



    Hope that helps!