Posts by smuzoen

    Re: Cide cycltes through specific range of textboxes

    You could use something like

    Dim tb As Object
    Dim counter As Integer
    For Each tb In Me.Controls
        counter = counter + 1
        If tb.Name = "TextBox" & CStr(counter) Then
            tb.Text = "test"
        End If
    Next tb
    End Sub

    I fixed the thread title for you.

    Re: £30 Automate Spreadsheet

    To attach the file select "Go Advanced" (just below the text box where you type in your message) and then use "Manage Attachments", Add File to select the excel document and then hit Done when finished. There is a limit on the file size so if you have any issues I will PM you my email address and you could always email it to me if you have trouble attaching the document. I would prefer to communicate via this thread however if needs be you can always email the document.

    Re: £30 Automate Spreadsheet

    I am going to need to get you to give me a sample workbook rather than a single graphic. You want a button that asks for the pattern to apply to a new client however I guess you will also want to enter the clients name as well and perhaps a time? From the image I cannot really discern the patterns to which you refer. I am not sure how the client exits the worksheet - is it after rotating through all 4 patterns or how does the client get removed? I am not sure if this is all on one worksheet or is distributed over multiple sheets.

    The best way to demonstrate what you require is to give me a sample workbook showing me how a client moves through the various patterns. You also need to define these patterns for me - what constitutes a "pattern". Some background would perhaps give me a better understanding as to your requirements.

    If you could create a sample workbook with a few dummy clients, include all 4 patterns to which you refer (making sure you show/define what constitutes a pattern) and show me exactly what has to happen step by step - i.e. how does the client rotate through the patterns. You say that the client stays in the pattern for 4 weeks then moves to the next - show me how you want the patterns displayed - is a 4 week block on 1 sheet or over multiple sheets. A sample workbook with dummy clients and some text boxes explaining how your algorithm works will make this clearer. The requirements are a little too vague at this point for me to make a start.

    If there is anything confidential that you do not want to divulge in this thread you can PM me however if we could communicate as much as possible via this thread that would be good.

    Finally what is the time frame for completion of this project?

    Re: Automated Paypal Invoicing Excel $50.00

    There are a few things that I require from you to customise/automate the template.
    1. I need your Business name
    2. If you have a logo you want to incorporate with the Paypal template could you send this to me
    3. I need your business email address
    4. I need to know how you want the invoice numbers generated (would be best to just auto-increment the invoice number e.g INV1, INV2, INV3 etc) - or this could be part of the excel data sheet you listed in your first post- have an additional column for Invoice Number.
    5. The payment terms (7 days, 30 days etc)
    6. Any terms and conditions that should be included on the Paypal invoice (optional)
    7. Any notes that should go onto the invoice (optional)

    The requests I have listed above basically are the fields on the template (some of these are just optional includes you can have on the template).

    You can PM me these details if you wish.

    So I can test the final code I am going to need to generate some invoices - I cannot invoice myself so what I did with the last project was to generate invoices to the person requesting the work (in this cause - you) - what that means is that I need to generate invoices to your Paypal account. I then just delete the invoices so as to cancel the charges. That means I need to have your PayPal email to generate invoices against your account. I will only need to do a few test invoices just to confirm it all works as expected. Any invoices I generate against your account I will obviously delete - PayPal will not charge any commissions/cancellation fees if I generate then delete the invoice

    If any of this is not clear let me know.

    As I said previously it may be best to PM these details to me - anything you send to me will be treated in the strictest confidence.

    Re: Automated Paypal Invoicing Excel $50.00

    If you are still interested in getting this developed I wrote the original solution you referred to in your initial post. I am sure I could modify this to what you require. Let me know if you are still interested in perusing this

    Re: Automatically Save As Date And Time Every x Minutes

    Please do not post new questions in old threads - create a new thread with your question and if you feel this thread is of relevance then simply reference the thread by adding a link in your post. This thread has been closed. You will need to create a new post.

    Re: Validate cell input character length based on another cell's text input

    Please when posting questions upload a sample workbook - it makes creating a solution easier.
    If I understand you correctly you want the service tag checked once the Make is entered into the sheet. One way is to have a Change Event in the Worksheet - so if the value in Column E is changed then the value in the same row in Column D is checked to :
    1. Make sure it is the correct length
    2. Make sure it only contains alphanumeric characters (no special characters)
    The length can be tested with "len" and the special characters can be tested with a regular expression.
    So in the worksheet you would have something like

    Then in a module (in this case I have named the module "Functions") have the following code (note - you only mentioned Dell and HP - you will need to adapt for additional rules you may have)

    See attached workbook - remember there is code in Worksheet 1 as well as the module called Functions

    Re: Creating an IF block (Pulling data from multiple files to affect macros)

    I think your problem is in

    'PartCode = [{C:\Users\ROB180\Documents\MACRO TESTING.xlsm}.Sheet1.Range("f8")]

    What I am saying is get this value BEFORE opening the CSV file and you will not need any { or [ syntax - it does not appear this value changes so get this value first then start iterating through the csv files - then just use basic string comparisons to see if there is a match - if you step through this code i think you will find it will generate an error that will not cause the code to error out. I am not talking about getting the value from the CSV file before opening it but the value from the workbook that you place into PartCode

    Re: Creating an IF block (Pulling data from multiple files to affect macros)

    Your making it difficult - why not get the value before opening the csv files - the value you are comparing from your description is in the workbook running the code so just change the order

    'get value before opening csv files
    partcode = ActiveWorkbook.Sheets(1).Range("F8")
    'iterate through csv files
    'compare value to partcode

    Re: Converting files from 2003 to 2007 in Excel

    Filesearch was deprecated in Excel 2007 - You can use the FileSystemObject -…g278516(v=office.14).aspx
    An example you can modify

    Re: using variable in criteria of autofilter macro xl 2007

    Sub testtwo()
    Dim x As String, y As String, cfind As Range
    x = ">>1"
    Range("A1:S798").AutoFilter field:=8, Criteria1:="=*" & x & "*"
    End Sub

    You do not need the


    Be careful of cut and paste coding - try to understand the code from first principles. I am not sure what the two variables "y" and "cfind" are for?
    Have a look at some tutorials

    Re: Code for finding duplicates to ignore empty Cells

    You did not include the specialcells - see attached workbook - I also commented out the deleting of the rows to show the data stays the same if you run the code more than once in Sub cleardups()

    You will have to delete the blank rows when you reset otherwise CurrentRegion will not work. You could use

    For Each r In Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column).SpecialCells(2)

    Re: Match Names to Email Address

    Welcome to the forum maree89. Please do not ask new questions in existing threads - please start a new thread and if you feel this thread is pertinent to your question then just add a link in your post to this thread. This thread has been closed. You will need to repost your question as a new post in the forum.

    Re: Code for finding duplicates to ignore empty Cells

    As snb has done to specifically modify Jindons code you would use specialcells(2) as the current region - exclude blank cells (for more information see

    However I do not understand why you ask


    How would one tweak this Code to ignore empty cells, so that it does not consider empty cells as duplicates?

    as Jindons code removes duplicates (or clears the contents of the cell) - so is the cell was empty anyway it would still be empty after the code has run. It does not delete the cell it just clears the contents of the cell and a cleared empty cell is an empty cell - do you see what I am getting at.

    However also try snb's code as this is shorter and quicker more efficient - to clear the actual cell just extend snb's code to

    There is a difference though - Jindons code removes the duplicates whereas snb's code leaves in the the first value and removes subsequent duplicates (correct me if I am wrong with your intent snb with respect to your code)

    Re: RankIF - what formula do i need

    NBVC your solution works perfectly - and my understanding is that this would be a better solution as it is much faster than SUMPRODUCT- is that what you have found with COUNTIFS v's SUMPRODUCT?
    This sample size is probably not large enough to notice the difference but on large data sets I have always found SUMPRRODUCT much slower :)

    I copied the data without the formulas and filtered/sorted the results to show that COUNTIFS does work - If OP has large data set I would go with NBVC's solution

    Re: adding a percentage in a progress bar without losing visuality

    It is working on the workbook I uploaded on my machine
    See attached workbook and images - sorry - really late - I am off to bed - perhaps someone else can pick this up for you. What version of Excel are you using.? The bar moves as I change the values so not sure what is the problem???