Posts by adygelber

adygelber · Dec 4th 2017

Hey,

Some tweak adjustments:

Code

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                                       (ByVal hwnd As Long, _
                                                        ByVal lpOperation As String, _
                                                        ByVal lpFile As String, _
                                                        ByVal lpParameters As String, _
                                                        ByVal lpDirectory As String, _
                                                        ByVal nShowCmd As Long) As Long






Sub CreatePDFForms()


    Dim PDFTemplateFile, NewPDFName, SavePDFFolder, LastName As String
    Dim ApptDate As Date
    Dim CustRow, LastRow As Long
    Dim Sheet1 As Worksheet


    Set Sheet1 = Sheets("Sheet1")


    With Sheet1
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        PDFTemplateFile = .Range("B16").Value
        SavePDFFolder = .Range("B18").Value
        ShellExecute 0, "Open", PDFTemplateFile, "", "", vbMaximizedFocus
        Application.Wait Now + 0.00006
        For CustRow = 5 To 5
            LastName = .Range("A" & CustRow).Value
            ApptDate = .Range("G" & CustRow).Value
        Next
    End With


End Sub

Display More

If in cell B16 you have a full URL (path) like "C:\MyDoc.pdf" it should be open.

For the rest of the code I am not so sure of the real meaning and purpose.

If you need further assistance please provide more details.

Cheers,
Adrian B.

adygelber · Dec 4th 2017

Hello,
Your second solution should work, even if it seems a little bit hard-coded..therefore something like this:

Code

If cbx1.Value = False Then
        Sheets("Sheet2").Columns(1).EntireColumn.Hidden = True
    Else
        Sheets("Sheet2").Columns(1).EntireColumn.Hidden = False
    End If

Of course, you can use an object like shtFinancial instead of Sheets("Sheet2"), if it is correctly Set

eg.

Code

Dim shtFinancial as worksheet
Set shtFinancial=Sheets("MySheet")

Please develop a little bit your needs or attach a sample workbook if you need further assistance.

Cheers,
Adrian B.

adygelber · Jul 30th 2017

Re: Modification in code

Hey,

Sorry for the late reply...

Try this and let me know if it is what you need:

Code

With Sheets("Sheet1")
        For i = 3 To .Cells(Rows.Count, "F").End(xlUp).Row
            Set myrange = .Range(.Cells(i, "F"), .Cells(i, "F").Offset(0, 2))
            mystr = ""
            For Each mycell In myrange
                If mycell.Value <> "" Then
                    If mystr = "" Then
                        mystr = mycell.Value
                    Else
                        mystr = mystr & "/" & mycell.Value
                    End If
                End If
            Next mycell
            .Cells(i, "F").Offset(0, 3).Value = mystr
        Next i
    End With

Display More

Cheers,
Adrian B.

adygelber · Jul 26th 2017

Re: Modification in code

Or:

Code

Dim myrange, mycell As Range
    Set myrange = Sheets("Sheet1").Range("F3", Range("F65536").End(xlUp))
    
    For Each mycell In myrange
        If WorksheetFunction.CountA(Range(mycell.Address & ":" & mycell.Offset(0, 2).Address)) = 3 Then
            mycell.Offset(0, 3) = mycell.Value & " " & "/" & " " & mycell.Offset(0, 1).Value & " " & "/" & " " & mycell.Offset(0, 2).Value
        End If
    Next mycell

adygelber · Jul 26th 2017

Re: Modification in code

Hey,

The simple way:

Code

For Each mycell In myrange 
        If mycell.Value<>"" and mycell.Offset(0, 1).Value<> "" and mycell.Offset(0, 2).Value <> "" then
            mycell.Offset(0, 3) = mycell.Value & " " & "/" & " " & mycell.Offset(0, 1).Value & " " & "/" & " " & mycell.Offset(0, 2).Value 
        End If
    Next mycell

adygelber · Jul 17th 2017

Re: Using VBA to refresh Excel Links embedded in PowerPoint

Hi,

There are some threads for this specific problem, on a Google search.

ex. http://www.vbaexpress.com/foru…ive/index.php/t-7279.html

Is this helping you?

Cheers,
Adrian B.

adygelber · Jul 17th 2017

Re: UserForm Find feature Question

Hi, SCOM,

Please see attached a simplified example and tell me if this is what you need.

forum.ozgrid.com/index.php?attachment/73130/

If no, maybe you can attach your WB to help us understand better your request.

Cheers,
Adrian B.

adygelber · May 5th 2017

Re: CASE not firing

Good point KjBox,
My mistake declaring as Double when textboxes stores only text

adygelber · May 5th 2017

Re: Applying VLookup in VBA to Filtered Data

Hey,

Try this and let me know:

Code

Application.ScreenUpdating = False
    
    Dim Lastrow As Long
    Dim ffr As Range


    Set Rng = ActiveSheet.UsedRange
    
    'your autofilter code (like Rng.AutoFilter field:=1, Criteria1:="ValueToFilter")
    
    Set ffr = Rng.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 10)


    ffr.Value = WorksheetFunction.VLookup(Rng.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1), Sheets("2016").Range("$A:$AN"), 10, False)
     
    Lastrow = ActiveSheet.UsedRange.Rows.Count
    
    With ffr
        .AutoFill Destination:=Range(Cells(ffr.Row, 10), Cells(Lastrow, 10)), Type:=xlFillDefault
    End With
     
    ActiveSheet.Calculate
    
    Application.ScreenUpdating = True

Display More

If you have an Subscript Out of Range error on your VLOOKUP please check your ranges.

Cheers,
Adrian B.

adygelber · May 5th 2017

Re: Auto populating text fields on websites and scraping the web

Hey,

I think it will be mission impossible to anyone helping you without seeing some code samples.
You should provide the HTML structure as is and a relevant piece of code, if you can't post the whole workbook.

Cheers,
Adrian B.

adygelber · May 5th 2017

Re: CASE not firing

Hey,

Try to put the code on the Change event of your textbox, like this:

Code

Private Sub txtnum1_Change()
    Dim V1 As Double
    V1 = txtnum1.Value
    Select Case V1
    Case Is < 1.51
        txtadj1.Value = "P"
    Case Is < 2.51
        txtadj1.Value = "US"
    Case Is < 3.51
        txtadj1.Value = "S"
    Case Is < 4.51
        txtadj1.Value = "VS"
    Case Is >= 4.51
        txtadj1.Value = "O"
    End Select
End Sub

Display More

Tell me if this worked.

Cheers,
Adrian B.

adygelber · Apr 28th 2017

Re: Shorter vba code

Hey,

Yes, you can declare n as integer.

Regards,
Adrian B.

adygelber · Apr 27th 2017

Re: Code to calculate total length installed based on number of pages that are entere

Glad to hear it!

Cheers,
Adrian B.

adygelber · Apr 27th 2017

Re: save and copy the data in to two sheets in userform

Please be more specific. What do you need to do?
I saw that you already have an userform. DO you want to make some improvements?

adygelber · Apr 27th 2017

Re: Shorter vba code

Hello IvCin,

Two ways to accomplish:

1. On Error Resume Next before With... and On Error GoTo 0 after End With

2. An workaround:

Code

Dim lr As Long
Dim i As Long
Dim wsNm As String
 
lr = Sheets("MASTER").Range("K" & Rows.Count).End(xlUp).Row


With Sheets("MASTER")
    For i = 2 To lr
        wsNm = .Cells(i, "K").Value
        n = 0
        For Each ws In Sheets
            If ws.Name = wsNm Then
                n = n + 1
            End If
        Next ws
        If n > 0 Then
            If .Cells(i, "L").Value = "H" Then
                Sheets(wsNm).Visible = False
            Else
                Sheets(wsNm).Visible = True
            End If
        End If
    Next i
End With

Display More

It's up to you

Cheers,
Adrian B.

adygelber · Apr 27th 2017

Re: Code to calculate total length installed based on number of pages that are entere

Hey,

You could try something like this:

Code

For Each ws In Sheets
        If ws.Name <> "MAIN" Then
            If isVal = "" Then
                isVal = ws.Range("B15")
            Else
                isVal = isVal & "," & ws.Range("B15")
            End If
        End If
    Next ws
    
    arr = Split(isVal, ",")
    
    n = 0
    For i = LBound(arr) To UBound(arr)
        n = n + arr(i)
    Next i
    
    Sheets("MAIN").Cells(1, 1) = n

Display More

Every time when you will run the code it will collect data from all the worksheets but MAIN.
Let me know if this worked.

Cheers,
Adrian B.

adygelber · Apr 27th 2017

Re: Shorter vba code

Hey,

Try this way:

Code

Dim lr As Long
    Dim i As Long
    Dim wsNm As String


    lr = Sheets("MASTER").Range("K" & Rows.Count).End(xlUp).Row
    
    With Sheets("MASTER")
        For i = 2 To lr
            wsNm = .Cells(i, "K").Value
            If .Cells(i, "L").Value = "H" Then
                Sheets(wsNm).Visible = False
            Else
                Sheets(wsNm).Visible = True
            End If
        Next i
    End With

Display More

Cheers,
Adrian B.

adygelber · Apr 27th 2017

Re: copy cell from sheet2 with criterias in sheet1

Hey,

I think that you will have to clarify a little bit the problem...

1. You spoke about sheet2, sheet1, sheet4, but in the attached WB I can see only two sheets: Automation and Population.
2. You said "Add 4 rows below cell 2". Well, "cell 2" is not quite a reference and can be interpreted. Maybe it will make more sense if you explain why do you need some rows.
3. You said "Add year (B7 +1) in Time column". Ok, so you need to increment the year 2013 but where to store the incremented values in the TIME column? And why to do this?
4. Step3 and Step4 are also unclear for me...

Maybe is my fault but I can't really see the logic behind this application.
I can't understand what you are trying to achieve and I am pretty sure that your question can find an answer if the logic behind is well defined.

Cheers,
Adrian B.

adygelber · Apr 27th 2017

Re: save and copy the data in to two sheets in userform

Hey,

Try this:
forum.ozgrid.com/index.php?attachment/72282/

Cheers,
Adrian B.

adygelber · Apr 27th 2017

Re: userform combobox populated from two columns of a table

Hello again,

I clearly misunderstood you request.

Please paste the above code in your userform code area:

Code

Private Sub CommandButton1_Click()


    If cmbExisting.Value <> "" Then Sheets(cmbExisting.Value).Select
    
End Sub


Private Sub UserForm_Initialize()


    Set myTbl = Sheets("Client Records").ListObjects("ClientRec")
    
    lr = myTbl.Range.Rows.Count
    
    For i = 2 To lr
        If arStr = "" Then
            arStr = Cells(i, Range("ClientRec[Col1]").Column) & " " & Cells(i, Range("ClientRec[Col6]").Column)
        Else
            arStr = arStr & "," & Cells(i, Range("ClientRec[Col1]").Column) & " " & Cells(i, Range("ClientRec[Col6]").Column)
        End If
    
    Next i


     
    ufExisting.cmbExisting.List = Split(arStr, ",")


End Sub

Display More

Here is the sample workbook:
forum.ozgrid.com/index.php?attachment/72281/

Cheers,
Adrian B.

Posts by adygelber

Compile Error: Variable not defined of OpenURL ""& zzzzzz & "", Show_Maximized

VBA - Hide column in every worksheet when Check Box is not selected

Modification in code

Modification in code

Modification in code

Using VBA to refresh Excel Links embedded in PowerPoint

UserForm Find feature Question

CASE not firing

Applying VLookup in VBA to Filtered Data

Auto populating text fields on websites and scraping the web

CASE not firing

Shorter vba code

Code to calculate total length installed based on number of pages that are entered

save and copy the data in to two sheets in userform

Shorter vba code

Code to calculate total length installed based on number of pages that are entered

Shorter vba code

copy cell from sheet2 with criterias in sheet1

save and copy the data in to two sheets in userform

userform combobox populated from two columns of a table