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: £30 Automate Spreadsheet

Cytop are you going to take this on? If not I would be interested depending on time frame.

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: Delete hidden rows in filtred table with VBA

Please read the Forum Rules before posting - you must include code tags when posting code - Rule 1. http://www.ozgrid.com/forum/announcement.php?f=8 I have added them for you this time.
To increase the chances of getting a reliable solution please also upload a sample workbook with dummy data and all sensitive data removed.

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim result As String
If Not Intersect(Target, Range("E" & Target.Row)) Is Nothing And Target.Count = 1 Then
    If Len(Target.Offset(, -1)) = 0 Then
        Exit Sub
    Else
        result = Functions.Validate(Target.Offset(, -1).Value, Target.Value)
        If Len(result) > 0 Then
            MsgBox result
            Target.Offset(, -1).Select
        End If
    End If
End If
End Sub

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)

Option Explicit


Function Validate(serviceTag As String, make As String) As String
Dim match As Integer




Select Case UCase(make)
    Case Is = "DELL"
        If Len(serviceTag) <> 7 Then
            Validate = "Service Tag must be 7 characters in length"
        End If
    Case Is = "HP"
        If Len(serviceTag) <> 10 Then
            Validate = "Service Tag must be 10 characters in length"
        End If
End Select
If Len(Validate) = 0 Then
    match = CheckChars(serviceTag)
    If match = 0 Then
        Validate = "Service Tag has invalid characters"
    End If
End If
End Function




Function CheckChars(tag As String) As Integer
Dim match As Object, Regex As Object


Set Regex = CreateObject("vbscript.regexp")
Regex.Pattern = "^[0-9a-zA-Z]+$"
Regex.Global = True


Set match = Regex.Execute(tag)
CheckChars = match.Count
End Function

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 - http://msdn.microsoft.com/en-u…g278516(v=office.14).aspx
An example you can modify

Option Explicit
Sub ListFiles()
    Dim fileFound As Object, fso As Object
    Dim folderSelected As String




    Set fso = CreateObject("Scripting.FileSystemObject")
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancel Selected"
            Exit Sub
        End If
        folderSelected = .SelectedItems(1)
    End With
    For Each fileFound In fso.GetFolder(folderSelected).Files
        MsgBox fileFound.Name
        'Do work
    Next
End Sub

Re: using variable in criteria of autofilter macro xl 2007

No need to apologise. Using quotes in VBA can be quite confusing. You were on the right track and using the immediate window is great technique for testing assignments and expressions.

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

Operator:=xlAnd

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 http://www.ozgrid.com/VBA/autofilter-vba-criteria.htm

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()

Sub cleardups()
    Dim r As Range, w, e, txt As String
    
'DelBlankRows
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each r In Range("a1").CurrentRegion.SpecialCells(2)
            If Not .exists(r.Value) Or r.Value = vbNullString Then
                .Item(r.Value) = VBA.Array(False, r.Address)
            Else
                w = .Item(r.Value)
                w(0) = True
                .Item(r.Value) = w
                r.ClearContents
            End If
        Next
        For Each e In .keys
            If .Item(e)(0) Then
                txt = txt & "," & .Item(e)(1)
                If Len(txt) > 240 Then
                    Range(Mid$(txt, 2)).ClearContents
                    txt = ""
                End If
            End If
        Next
    End With
    If Len(txt) Then Range(Mid$(txt, 2)).ClearContents
    Range("A:E").Interior.ColorIndex = 0
End Sub

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 http://msdn.microsoft.com/en-us/libr.../ff196157.aspx)

Sub test() 
    Dim r As Range, w, e, txt As String 
    With CreateObject("Scripting.Dictionary") 
        .CompareMode = 1 
        For Each r In Range("a1").CurrentRegion.SpecialCells(2) 
            If Not .exists(r.Value) Then 
                .Item(r.Value) = VBA.Array(False, r.Address) 
            Else 
                w = .Item(r.Value) 
                w(0) = True 
                .Item(r.Value) = w 
                r.ClearContents 
            End If 
        Next 
        For Each e In .keys 
            If .Item(e)(0) Then 
                txt = txt & "," & .Item(e)(1) 
                If Len(txt) > 240 Then 
                    Range(Mid$(txt, 2)).ClearContents 
                    txt = "" 
                End If 
            End If 
        Next 
    End With 
    If Len(txt) Then Range(Mid$(txt, 2)).ClearContents 
End Sub

However I do not understand why you ask

Quote

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

Sub M_snb() 
    With CreateObject("scripting.dictionary") 
        For Each it In Sheets(1).Cells(1).CurrentRegion.SpecialCells(2) 
            If .exists(it.Value) Then 
                it.ClearContents 
            Else 
                .Add it.Value, 1 
            End If 
        Next 
        MsgBox Join(.keys, vbLf) 
    End With 
End Sub

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: Search and Replace text

Client never responded - will close thread as no correspondence received.

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???