Posts by tostrowski

  • Pull Out MAX/MIN based upon begining of number in an array

    I am looking for the proper formula/VBA code to find the MIN/MAX based upon the starting two numbers in an array.


    Example:
    Array...


    25123
    26123
    25987
    26987


    I want to get the MIN/MAX for all values that begin with 25. I feel that I am close with my formula, but I just can't seem to pull out the correct number.


    Any help would be appreciated. Thank you.

  • Get Month Name From Cell Number & Use In File Path & Name

    I have searched the site and have been unable to find what I am looking for. I could be simply searching for the wrong thing, but I am looking to esentially create a variable within a file path name.


    Example:


    C:\Folder\[COLOR="Red"]May2008[/COLOR]\[filename.exls]Sheet1'!$A$1


    I would like to make the "May2008" a variable that I have linked to another cell. Esentially I want to be able to change the cell (May2008) to whatever date I want and have it change all the file paths associated with it.


    I have tried concatenate, "&" and even some simple code, but no luck to actually having excel capture the "linked" data.


    Any help is greatly appreciated. Thank you.

  • Countif With Dynamic Cell Reference

    Hello everyone! This is probably an easy fix for someone, but for some reason I am stumped on why this is not working. I have data like below....
    A
    100
    250
    213
    154
    523
    466


    I want to find all data GREATER THAN another cell. In this example lets say cell B1 is "300". The logic in my mind is


    =COUNTIF($A$1:$A$6,"<B1")


    This does not work, it returns zero.


    if I put =COUNTIF($A$1:$A$6,"<300") then it would work, but I would like to make this dynamic.


    Thank you for your help in advance!


    --Tony O.

  • Searching a range for numbers

    It has been a while since working with code and I can't seem to find something similar to this on the site, but I know that this will be a quick fix for someone out there.


    I am trying to search a range for certain numbers. If they are there I would like for it to pop up a message box. I have narrowed it down to it being the range to which it is giving me an error 13 type mismatch.


    Thank you in advance for your help.


    Code
    If Range("B3:B100") = "8800" Or "9900" Then
       MsgBox "You have incorrect...."
  Else: MsgBox "Correct"
    
End If

  • Looping through worksheets

    I want to loop through the worksheets in workbook, but in my case I want to loop through all the worksheets except the first which stands as like a cover page. Is there a way to start at the 2nd page and loop through the rest?


    I found this code out on the site and this looks like how I would start.

    Code
    Dim wb As Workbook 
Dim ws As Worksheet 
 
Set wb = ActiveWorkbook 
 
For Each ws In wb.Worksheets 
     ' do something with worksheet ws
Next ws


    Thank you.

  • Passing variables into new Sub

    Re: Passing variables into new Sub


    Here is a snipet of the code, to give you an idea. What I actually want to do is to repeat this process but for another workbook and cell range, so knowing what I know I was just going to call a new sub routine. But I am just a rookie and don't know the quick ways in VBA.


  • Passing variables into new Sub

    Re: Passing variables into new Sub


    Well, being the rookie I am, that is what I am trying to figure out.


    I have created a Private Sub CommandButton1_Click routine and have executed code in that and then want to call up a sub routine, but I don't know how to get the variables to pass to the new Sub. I hope that this explains it a little better.


    Thank you.

  • Passing variables into new Sub

    I have two input boxes in a userform that is selecting variables. I need to have these variables passed to a new Sub routine so that I can continue the code?


    I know that someone will know this right away.


    Thank you in advance!

  • Input boxes to replace data

    Re: Input boxes to replace data


    Thank you, I just figured it out while you were replying to me.


    ascalese: I am not sure how the INDIRECT works, but if it does work across multiple books, can you help?

  • Input boxes to replace data

    Re: Input boxes to replace data


    Sorry, I know alot about VBA, but I am just learning and everything that I have learned has been over the past 4 months.


    I am not sure what you mean by the indirect method?

  • Input boxes to replace data

    Re: Input boxes to replace data


    Well I am trying to replace (or update) certain links in a spreadsheet.


    The data is linked to another spreadsheet, which changes monthly. Not only does the filename change but so does the column the data is in. So in the attached, the May E1, will change to May E2, Jun E1, Jun E2, etc...

  • Input boxes to replace data

    Re: Input boxes to replace data


    Now I am going to throw you a curve ball. I don't know if you will know this, but I have attached an updated spreadsheet with some issues that I run into.


    In the sheet the cells are not necessarily in consecutive order (I should have given a better example before). And there are total columns within the series.


    I too have learned everything that I know from this site, and I continue to learn more everytime I use the site.

  • Input boxes to replace data

    Re: Input boxes to replace data


    I can't believe I missed the apostophe!


    But I am for some season getting an application error when I get to that line in the code.


    In the code I have REMd out what the root of the file should look like. I must be missing something little.


    Yes, if you could help me with the If statement, that would be great!


    Also, how would I run the form from a macro?


    Sorry to be so needy! I am still learning, but thank you soo much!!

  • Input boxes to replace data

    Re: Input boxes to replace data


    The program seems to be working great and it looks like I will be able to manipulate it the way that I want to, but I am not familiar with the For Loop. I am not really sure what it is doing.


    Could you give me a quick explanation on what this does? Thank you!



    Code
    For X = 0 To 4


StartCell.Offset(X, 0).FormulaR1C1 = "'=C:\[shipments " & str & " " & str2 & ".xls]SHEET1$c[-2]r[-2]"


Next

  • Input boxes to replace data

    I am trying to use an input box to change certain cells within a data set. I have attached an example of what I am trying to do. In the example I am trying to change the "May" and the "F1" cells within the formula using the prompts entered by the user. I can create the input boxes, but I don't know how to write the code to deal with the users inputed data. I know that I could write 12 IF THEN statements representing each month, but I know that there has to be a better way.


    So for the attached example I would want to change May to Jun and F1 to F2.


    I know this probably confusing, so if you have any questions please let me know. Thank you for any suggestions or help!

  • Zero in blank fields

    I am trying to put a zero in all numeric fields within a data set. There are both number fields and text fields. Some of the cells have no data in them and I would like to replace those cells with the number "0". Any ideas on how to complete that?


    Here is the way that I thought I should go, but I have no clue on where to go exactly.

    Code
    Selection.SpecialCells(xlCellTypeBlanks).Select


    Thank you in advance!