Posts by norie

**norie** · Jul 29th 2016

Re: Loop through values in userform to store in array

What do you want to know?

By the way, are you going to be sticking with the multiple rows of controls or are you considering the suggestion I made about using a listbox?

If it's the former it's not a problem really though the code required might be a bit more complicated.

**norie** · Jul 29th 2016

Re: Loop through values in userform to store in array

How exactly is the data organised?

Are you working with merged cells?

By the way you can extract the individual columns from the array using Application.Index.

For example this will put the values from column 2 in the listbox into column C on Sheet1 starting at row 2.

Code

Dim arrData
Dim arr2ndCol


    arrData = ListBox1.List
    
    ReDim Preserve arrData(UBound(arrData), ListBox1.ColumnCount - 1)
    
    arr2ndCol = Application.Index(arrData, , 2)
    
    Sheets("Sheet1").Range("C2").Resize(UBound(arr2ndCol, 1) + 1, UBound(arr2ndCol, 2) + 1).Value = arr2ndCol

Display More

**norie** · Jul 29th 2016

Re: Loop through values in userform to store in array

It depends where you want to put the data.

If we extend the last code I posted this will put the contents of the listbox on Sheet1 starting at A1.

Code

Sheets("Sheet1").Range("A1").Resize(UBound(arrData, 1) + 1, UBound(arrData, 2) + 1).Value = arrData

**norie** · Jul 29th 2016

Re: Loop through values in userform to store in array

Not sure about the nested loop thing, what would that be used for?

If you were using a listbox you can put all the data in an array in one go like this.

Code

Dim arrData()


    arrData = ListBox1.List
    
    ReDim Preserve arrData(UBound(arrData), ListBox1.ColumnCount - 1) ' resize to remove Nulls

**norie** · Jul 28th 2016

Re: Combobox.Value in Range do else

Perhaps.

Code

Private Sub ComboBox1_Change()
Dim MMod As Range
Dim Res As Variant


    Set MMod = Worksheets("Validate").Range("R2:R8")


    Res = Application.Match(ComboBox1.Value, MMod, 0)


    If Not IsError(Res) Then
        TextBox1.Value = MMod(Res).Offset(, 1).Value
    Else
        TextBox1.Value = "Zero"
    End If


End Sub

Display More

**norie** · Jul 28th 2016

Re: Loop through values in userform to store in array

The controls seem to be consistently named so you should be able to loop through them using their names.

Actually, I'll take that back, some of the controls are named consistently, specifically the first 3 rows on the Materials tab.

If all the controls were named consistently you could use something like this.

Code

Dim arrMaterials(1 To 21, 1 To 5)
Dim I As Long


   
    For I = 1 To 21
        arrMaterials(I, 1) = Me.Controls("cmb_Materials_Line" & I & "_Item").Value
        arrMaterials(I, 2) = Me.Controls("txt_Materials_Line" & I & "_Color").Value
        arrMaterials(I, 3) = Me.Controls("txt_Materials_Line" & I & "_Qty").Value
        arrMaterials(I, 4) = Me.Controls("txt_Materials_Line" & I & "_Price").Value
        arrMaterials(I, 5) = Me.Controls("txt_Materials_Line" & I & "_Extended").Value
    Next I

Display More

By the way, have you considered using a multi-column listbox, one line of controls and a command button for materials?

The user would select/enter data in the line of controls and then click the command button to add them to the listbox.

You could also have buttons to edit/delete rows in the listbox.

With that setup it would be easier to work with the data on the Materials tab.

**norie** · Jul 17th 2016

Re: Unable to click on a link from webpage

What have you tried?

**norie** · Jul 15th 2016

Re: Copying certain sheets into one worksheet

Try this.

Code

Sub CopyAll()
Dim ws As Worksheet


    On Error Resume Next
    If Err Then
        On Error GoTo 0
    Else
        Sheets("Sheets Combined").Select
        Range("A2:AC2000").Select
        Selection.AutoFilter Field:=1, Criteria1:="<>"
        Sheets("Sheets Combined").ShowAllData
        Sheets("Sheets Combined").Select
        Range("A3:AC2000").Select
        Range("A3:AC2000").ClearContents
        Range("A3:AC2000").ClearFormats
        Range("A3").Select
    End If




    'Sort and copy data


    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.CodeName
            Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9"
            Case Else
                ws.Activate
                Range("A3:AC2000").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
                                        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                                        DataOption1:=xlSortNormal
                Range("A3").Select
                ActiveSheet.Range("A3:AC2000").Copy
                Sheets("Sheets Combined").Select
                Range("A3").Select
                emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
                ActiveSheet.Cells(emptyRow, 1).PasteSpecial
                Range("A3").Select
                Sheets("Sheets Combined").Select
                Range("A2").Activate
                Range("A2:AC2000").Select
                Selection.AutoFilter Field:=1, Criteria1:="<>"
                Worksheets("Sheets Combined").ShowAllData
                ActiveSheet.Range("A3").Select
                ws.Activate
                Range("A2:AC2000").Select
                Selection.AutoFilter Field:=1, Criteria1:="<>"
                ws.ShowAllData
                Range("A3").Select
                Sheets("Sheets Combined").Select
                Range("A3").Select
        End Select
    Next ws




    '   Display confirmation message


    Sheets("Review Template").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "1:14"
        .PrintTitleColumns = ""
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .Order = xlDownThenOver
        .BlackAndWhite = True
        .Zoom = False
        .FitToPagesTall = 200
        .FitToPagesWide = 1
    End With
    
    ActiveSheet.Range("A1").Select


    MsgBox "Task Complete", vbInformation, "Task Completed"
    
End Sub

Display More

**norie** · Jul 14th 2016

Re: Object Variable or With Block Variable not set. Quite confused here.:(

If the changed cell is not in the range A2:A10 this, existing, code tells VBA to exit the sub.

Code

If Not Application.Intersect(Target, Range("A2:A10")) Is Nothing Then Exit Sub

**norie** · Jul 14th 2016

Re: Object Variable or With Block Variable not set. Quite confused here.:(

Not 100% sure what you are doing but if you want the code to run when a change is made in A2:A10 try this.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyObject As Object    ' Create object variable.


    Set MyObject = UserForm2
    
    Application.EnableEvents = False


    If Not Application.Intersect(Target, Range("A2:A10")) Is Nothing Then Exit Sub


    If Target.Value = "" Then
        UserForm1.Hide ' is UserForm1 actually on display?
    Else
        Sheet1.Shapes("Button 1").Visible = msoFalse
        Sheet3.Range("C8:C9").ClearContents
        UserForm1.Show
    End If


    Application.EnableEvents = True


End Sub

Display More

**norie** · Jul 14th 2016

Re: Fix loop for cell reference totally stuck

What sheet are Range and.Cells meant to refer to here?

Code

Range(Sheet1.Range(.Cells((42 + x - 3), 6).Value, .Cells((42 + x - 3), 7).Value)).Copy

What do you actually have on Sheet1?

**norie** · Jul 14th 2016

Re: Object Variable or With Block Variable not set. Quite confused here.:(

Cells A2 and A10 can never intersect so Intersect(Range("A2"), Range("A10")) will return Nothing.

What are you trying to do?

**norie** · Jul 13th 2016

Re: The yes/ no function

What exactly isn't working?

**norie** · Jul 11th 2016

Re: Selecting Large data range for pivot table

That's probably the only Excel shortcut I know.:)

**norie** · Jul 8th 2016

Re: Autofill from start date to end date

yogesh.pisal

Could you please start a new thread?

**norie** · Jul 8th 2016

Re: Comma Between the CheckBox Values into one cell

Don't you already have code to do that behind the Delete button?

**norie** · Jul 8th 2016

Re: Selecting a Large data range for a pivot table

If you put the cursor somewhere in the data you can use CTRL+SHIFT+* to select the current region.

**norie** · Jul 8th 2016

Re: Comma Between the CheckBox Values into one cell

asmita.nda

I've moved the post from the new thread you started to this thread.

**norie** · Jul 8th 2016

Re: Comma Between the CheckBox Values into one cell

If you want to put a space after the comma just change "," to ", " in the Join function code I posted.

Code

ws.Range("J" & lngWriteRow).Value = Join(arrOuput, ", ")

**norie** · Jul 7th 2016

Re: Comma Between the CheckBox Values into one cell

In your Iif statements you have ws.Range("J", lngWriteRow) it should be ws.Range("J" & lngWriteRow).

By the way, here's another way you could handle the checkboxes.

Code

Dim cnt As Long
Dim I As Long
Dim arrOutput()
Dim arrSectors As Variant


    ' other code


    arrSectors = Array("Construction", "Infrastructure", "Consultant", "Resources/Mining", "Supplier", "Education", _
                       "Competitor", "Friend", "Government", "Commercial Retail", "Private Company", "Health Planning")


    For I = LBound(arrSectors) To UBound(arrSectors)
        If Me.Controls("CheckBox" & I + 1).Value = True Then
            ReDim Preserve arrOutput(cnt)
            arrOutput(cnt) = arrSectors(I)
            I = I + 1
        End If
    Next I


    ws.Range("J" & lngWriteRow).Value = Join(arrOuput, ",")

Display More