Posts by 6StringJazzer

No. You can use a larger font, which make the rows taller to accommodate the font, but you can't set the row height to add padding.

Using ActiveCell is usually not a good idea because the code may not have control over what cell is active. What exactly are you trying to do there?

Your description is very generic so it is not possible to give you code that you can simply plug in.

Where is the ListBox? What is its name? If on a worksheet, is it ActiveX or Forms?
Where is the button? What is its name? If on a worksheet, is it ActiveX or Forms?
What worksheet has the value you want to make active?

Is that worksheet active when you click the button

Something like this:

Public Sub CommandButton1_Click()

   Dim Found As Range
   Set Found = Worksheet("Sheet 1").Range("A:A").Find(What:=Listbox1.List(0), lookin:=xlValues, lookat:=alWhole)
   If Found Is Nothing Then
      MsgBox Listbox1.List(0) & " not found in worksheet column A"
   Else
      Worksheet("Sheet 1").Activate ' may not be necessary if this sheet is already active
      Found.Select
   End If

End Sub

You're welcome! Glad I was able to help.

After you delete the sheets you have to save the workbook before you attach it. Attach retrieves the file from the Windows file system, not from Excel's memory.

ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Save

Your code seems to cover everything except the message. Also you have four mutually exclusive conditions so should not use serial If statements.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Column >= 1 And Target.Column <= 4 Then
       If Target = "" Then
           MsgBox "You Can’t Leave Cell Blank Please Enter any Value to Move to Next Cell"
           Target.Select
       Else
          Select Case Target.Column
             Case 1: Range("B" & Target.Row).Select
             Case 2: Range("C" & Target.Row).Select
             Case 3: Range("D" & Target.Row).Select
             Case 4: Range("A" & Target.Row + 1).Select
          End Select
       End If
    End If

End Sub

I answered your question here

Ciao! Not sure what you mean by "i can't use the same name as the original file + variable part".

What name are you using now? It looks like you are using the contents of the field "codice" as the file name.

What name do you want to use instead?

This code takes the text in the cell as input and returns an array (1 to 4) with name, number, company name and sender's email address.

Public Function Matches(Subject As String) As String()


Dim RE As Object
Dim MatchList As Object
Dim Match As Variant
Dim i As Long
Dim MatchArray(1 To 4) As String


Const Pattern = ".*Name\s*(.*)\s*Telephone\s*No.\s*(.*)\s*Email\s*(.*)\s*Company\s*Name\s*(.*)\s*Company\s*Registration.*"


Set RE = GetRE(Pattern:=Pattern, IgnoreCase:=False, GlobalMatch:=False)


Set MatchList = RE.Execute(Subject)


For i = 1 To 4


For Each Match In MatchList
MatchArray(i) = RE.Replace(Match, "$" & i)
Next Match


Next i


Set RE = Nothing
Matches = MatchArray


End Function


' Return a RexExp object
Public Function GetRE(Optional Pattern As String = "", _
Optional IgnoreCase As Boolean = False, _
Optional GlobalMatch As Boolean = True) As Object


Set GetRE = CreateObject("vbscript.regexp")


GetRE.Pattern = Pattern
GetRE.IgnoreCase = IgnoreCase
GetRE.Global = GlobalMatch


End Function

I showed all the choices for completeness so it would be clear the logic I was using. You don't need to use all of them.

I tested your idea and it seems to work.

UBound will raise an error if the argument is not an array. If the element is a scalar, like a Long or String, that will cause an error.

I also suggest making the loop constraints dynamic.

I think you want something like this:

   Dim Possible(1 To 9, 1 To 9) As Variant
   
   Dim L As Long
   Dim i As Long, j As Long
   
   For i = LBound(Possible, 1) To UBound(Possible, 1)
      For j = LBound(Possible, 2) To UBound(Possible, 2)
      
         If IsEmpty(Possible(i, j)) Then
            Debug.Print "Possible (" & i & ", " & j & ") is empty"
            ' Has not been assigned a value
         
         ElseIf InStr(TypeName(Possible(i, j)), "(") > 0 Then
            Debug.Print "Possible (" & i & ", " & j & ") is an array"
            ' Do you need to know the dimensions if this is an array? That's a bit tricky

         Else
            Debug.Print "Possible (" & i & ", " & j & ") is " & TypeName(Possible(i, j))
               'scalar, do some stuff here
            
         End If
      Next j
   Next i

Yes, it's hard to pull four random numbers from a list of 2

You have the line commented out to copy the items to the worksheet (line 22). I fixed that and your code ran OK for me.

However, the problem could be in your data. This code will randomly choose cells in column B until it has found four unique values. If it contains many cells but many duplicate values, it could take some time to find four unique values. If it does not contain four unique values, your code will be in an infinite loop. Remember also that RANDBETWEEN can return the same number more than once. Can you show a sample of the data for the sheet where it gets stuck? You could add some Debug.Print statements at key points to trace what is going on.

I rewrote your code to eliminate that GoTo, an undesirable practice:

Sub RandomTickets()

    Dim x, i As Long, ii As Long, ws As Excel.Worksheet
   
    For Each ws In Sheets
    
        If ws.Name <> ActiveSheet.Name And ws.Name <> "Utilization Data" And ws.Name <> "Current Roster" Then
            With ws
                x = .Cells(1).CurrentRegion.Columns(2)
'                On Error Resume Next
'                With CreateObject("system.collections.arraylist")
                 With CreateObject("Scripting.Dictionary")
                
                    For i = 1 To 4
                    
                        Do
                           ii = Application.RandBetween(2, UBound(x, 1))
                        Loop Until Not .exists(x(ii, 1))
                        
                        .Add x(ii, 1), x(ii, 1)
                    
                    Next
'                    ws.[H2:K2] = .toarray
                     ws.[H2:K2] = .keys ' do not comment this out!
                End With
            End With
        End If

The Dictionary might work for you. It also has the Add and Exists (similar to Contains) methods so would require minimal code change there.

However, neither the Dictionary nor the Collection has a ToArray method so you would have to rewrite that line to explicitly extract all the items to the desired range.

The code in your first post does not work in the file you attached so I am at a dead end.

When I tried the sort approach you just showed, the sort order did not match the example you gave in the file. Also, the table in the Calculation sheet is Table2, not Table1 as shown in your latest code.

Quote from mamun.ges

Here in W10 =CONCATENATE(B10,O10) and in B10 I have ID and O10 =IF(B10="","",MONTH(H10) & YEAR(H10))

And none of that is anywhere in the file you attached. So I really don't understand what you're doing.

Glad you worked it out.

But how did the data get into the Calculation sheet to start?

Your file has no code in it.

This data is a bit strange. It is formatted as General but appears to be text since it is left-justified and some numbers have a leading 0. However, when I do an Excel sort, I get a numeric sort. Your example shows that you want a text sort.

How did this data get into your Excel file?

What version of Excel are you using?

You may want to consider updating your profile to indicate that. If you are using Excel 365 a formula solution is possible with no VBA.

Here is a version that suppresses screen updates so may run a little faster.

KEW115=sort tabs.xlsm

Here is the code in a test workbook. It is very easy to understand how this code works. It writes the tab names to a temporary worksheet then uses a worksheet sort to sort them, rather than building a sort algorithm in VBA. This is not super fast. For 246 tabs it might take a few minutes to run. Moving tabs is time-consuming.KEW115=sort tabs.xlsm