+ Reply to Thread
Results 1 to 6 of 6

VBA Code assistance for Hyperlink populate Loop

Hybrid View

  1. 07-14-2016, 05:36 AM #1
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,520

    VBA Code assistance for Hyperlink populate Loop

    Hello World

    I have the following code which opens hyperlink and populates specific sheet with information,saves and closes. This works well for my first hyperlink in Range("C7"). I have hyperlinks down the entire column C. How can I amend code to add a loop to go down column and populate all linked sheets. 

    Sub StatPop()
Dim rng As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Payment transactions").Activate
Range("C7").Hyperlinks(1).Follow
Sheets("Statement").Activate

  
Set rng = ThisWorkbook.Sheets("Payment Transactions").Range("J7")
  
If rng = "50% Deposit" Then
    Sheets("Statement").Range("b14").Value = ThisWorkbook.Sheets("Payment Transactions").Range("I7").Value
    Sheets("Statement").Range("c14").Value = ThisWorkbook.Sheets("Payment Transactions").Range("J7").Value
    Sheets("Statement").Range("d14").Value = ThisWorkbook.Sheets("Payment Transactions").Range("H7").Value
Else
If rng = "30% Payment" Then
    Sheets("Statement").Range("b15").Value = ThisWorkbook.Sheets("Payment Transactions").Range("I7").Value
    Sheets("Statement").Range("c15").Value = ThisWorkbook.Sheets("Payment Transactions").Range("J7").Value
    Sheets("Statement").Range("d15").Value = ThisWorkbook.Sheets("Payment Transactions").Range("H7").Value
Else
    Sheets("Statement").Range("b16").Value = ThisWorkbook.Sheets("Payment Transactions").Range("I7").Value
    Sheets("Statement").Range("c16").Value = ThisWorkbook.Sheets("Payment Transactions").Range("J7").Value
    Sheets("Statement").Range("d16").Value = ThisWorkbook.Sheets("Payment Transactions").Range("H7").Value
End If
End If

ThisWorkbook.Saved = True
Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
    Reply With QuoteRegister To Reply
  2. 07-14-2016, 06:07 AM #2
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,520

    Re: VBA Code assistance for Hyperlink populate Loop

    Hi All

    I've come up with following - Not sure if I am on the right track. Comes up with overflow error. Please HELP

    Sub StatPop()
Dim rng As Range
Dim numRow As Integer
Dim x As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Payment transactions").Activate
numRow = 2
Do While ActiveSheet.Range("c" & numRow).Hyperlinks.Count > 0
    ActiveSheet.Range("c" & numRow).Hyperlinks(1).Follow
    Sheets("Statement").Activate

    NumRows = Range("J2", Range("j2").End(xlDown)).Rows.Count
    Range("j2").Select
    For x = 1 To NumRows
    
        If NumRows = "50% Deposit" Then
            Sheets("Statement").Range("b14").Value = ThisWorkbook.Sheets("Payment Transactions").Range("I" & NumRows).Value
            Sheets("Statement").Range("c14").Value = ThisWorkbook.Sheets("Payment Transactions").Range("J" & NumRows).Value
            Sheets("Statement").Range("d14").Value = ThisWorkbook.Sheets("Payment Transactions").Range("H" & NumRows).Value
            Else
        If NumRows = "30% Payment" Then
            Sheets("Statement").Range("b15").Value = ThisWorkbook.Sheets("Payment Transactions").Range("I" & NumRows).Value
            Sheets("Statement").Range("c15").Value = ThisWorkbook.Sheets("Payment Transactions").Range("J" & NumRows).Value
            Sheets("Statement").Range("d15").Value = ThisWorkbook.Sheets("Payment Transactions").Range("H" & NumRows).Value
        Else
            Sheets("Statement").Range("b16").Value = ThisWorkbook.Sheets("Payment Transactions").Range("I" & NumRows).Value
            Sheets("Statement").Range("c16").Value = ThisWorkbook.Sheets("Payment Transactions").Range("J" & NumRows).Value
            Sheets("Statement").Range("d16").Value = ThisWorkbook.Sheets("Payment Transactions").Range("H" & NumRows).Value
        End If
        End If

    ActiveCell.Offset(1, 0).Select
    Next
    
numRow = numRow + 1

Loop


Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
    Last edited by Sintek; 07-14-2016 at 09:16 AM.
    Reply With QuoteRegister To Reply
  3. 07-14-2016, 09:26 AM #3
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,520

    Re: VBA Code assistance for Hyperlink populate Loop

    Change Dim from Integer to Long now code runs non stop in a loop ?
    Reply With QuoteRegister To Reply
  4. 07-14-2016, 11:21 AM #4
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,520

    Re: VBA Code assistance for Hyperlink populate Loop

    Hi Guys

    Code is now as below. What I need it to do is as follows:

    Open Hyperlink, Select sheet, populate sheet, Save sheet and close.
    Then move down to next row and repeat. I am LOST.

    Sub StatPop()
Dim RefRow As String
Dim numRow As Long
Dim x As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Payment transactions").Activate
numRow = 2
RefRow = Workbooks("Debtors").Sheets("Payment transactions").Range("J" & numRow)

Do While Workbooks("Debtors").Sheets("Payment transactions").Range("c" & numRow).Hyperlinks.Count > 0
    Workbooks("Debtors").Sheets("Payment transactions").Range("c" & numRow).Hyperlinks(1).Follow
    Sheets("Statement").Activate
    
    
        If RefRow = "50% Deposit" Then
            Sheets("Statement").Range("b14").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("I" & numRow).Value
            Sheets("Statement").Range("c14").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("J" & numRow).Value
            Sheets("Statement").Range("d14").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("H" & numRow).Value
            Else
        If RefRow = "30% Payment" Then
            Sheets("Statement").Range("b15").Value = Workbooks("Debtors")("Payment Transactions").Range("I" & numRow).Value
            Sheets("Statement").Range("c15").Value = Workbooks("Debtors")("Payment Transactions").Range("J" & numRow).Value
            Sheets("Statement").Range("d15").Value = Workbooks("Debtors")("Payment Transactions").Range("H" & numRow).Value
        Else
            ActiveSheets("Statement").Range("b16").Value = Workbooks("Debtors")("Payment Transactions").Range("I" & numRow).Value
            Sheets("Statement").Range("c16").Value = Workbooks("Debtors")("Payment Transactions").Range("J" & numRow).Value
            Sheets("Statement").Range("d16").Value = Workbooks("Debtors")("Payment Transactions").Range("H" & numRow).Value
        End If
        End If
    
    
    ActiveWorkbook.Close SaveChanges:=True
    
   
    numRow = numRow + 1

Loop


Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
    Last edited by Sintek; 07-14-2016 at 12:46 PM.
    Reply With QuoteRegister To Reply
  5. 07-14-2016, 01:16 PM #5
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,520

    Re: VBA Code assistance for Hyperlink populate Loop

    Hey there World

    I've got my code to run without errors. It is however not populating as it should. 

    Sub StatPop()
Dim RefRow As String
Dim numRow As Integer
Dim x As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Payment transactions").Activate
numRow = 2
RefRow = Workbooks("Debtors").Sheets("Payment transactions").Range("J" & numRow).Value

Do While Workbooks("Debtors").Sheets("Payment transactions").Range("c" & numRow).Hyperlinks.Count > 0
    Workbooks("Debtors").Sheets("Payment transactions").Range("c" & numRow).Hyperlinks(1).Follow
    Sheets("Statement").Activate
    
    
        If RefRow = "50% Deposit" Then
            Sheets("Statement").Range("b14").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("I" & numRow).Value
            Sheets("Statement").Range("c14").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("J" & numRow).Value
            Sheets("Statement").Range("d14").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("H" & numRow).Value
            Else
        If RefRow = "30% Payment" Then
            Sheets("Statement").Range("b15").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("I" & numRow).Value
            Sheets("Statement").Range("c15").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("J" & numRow).Value
            Sheets("Statement").Range("d15").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("H" & numRow).Value
        Else
            Sheets("Statement").Range("b16").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("I" & numRow).Value
            Sheets("Statement").Range("c16").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("J" & numRow).Value
            Sheets("Statement").Range("d16").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("H" & numRow).Value
        End If
        End If
    
    
    ActiveWorkbook.Close SaveChanges:=True
    
   

Sheets("Payment transactions").Activate
numRow = numRow + 1
Loop


Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
    Reply With QuoteRegister To Reply
  6. 07-14-2016, 01:24 PM #6
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,520

    Re: VBA Code assistance for Hyperlink populate Loop

    Eureka !!!!. I managed to solve it.

    Sub StatPop()
    Dim RefRow As String
    Dim numRow As Integer
    Dim x As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Sheets("Payment transactions").Activate
    numRow = 2


    Do While Workbooks("Debtors").Sheets("Payment transactions").Range("c" & numRow).Hyperlinks.Count > 0
    Workbooks("Debtors").Sheets("Payment transactions").Range("c" & numRow).Hyperlinks(1).Follow
    Sheets("Statement").Activate

    RefRow = Workbooks("Debtors").Sheets("Payment transactions").Range("J" & numRow).Value

    If RefRow = "50% Deposit" Then
    Sheets("Statement").Range("b14").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("I" & numRow).Value
    Sheets("Statement").Range("c14").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("J" & numRow).Value
    Sheets("Statement").Range("d14").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("H" & numRow).Value
    Else
    If RefRow = "30% Payment" Then
    Sheets("Statement").Range("b15").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("I" & numRow).Value
    Sheets("Statement").Range("c15").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("J" & numRow).Value
    Sheets("Statement").Range("d15").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("H" & numRow).Value
    Else
    Sheets("Statement").Range("b16").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("I" & numRow).Value
    Sheets("Statement").Range("c16").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("J" & numRow).Value
    Sheets("Statement").Range("d16").Value = Workbooks("Debtors").Sheets("Payment Transactions").Range("H" & numRow).Value
    End If
    End If


    ActiveWorkbook.Close SaveChanges:=True



    Sheets("Payment transactions").Activate
    numRow = numRow + 1
    Loop


    Application.ScreenUpdating = True
    Application.EnableEvents = True

    End Sub
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hyperlink Assistance, Please
    By Sunnypat in forum Excel General
    Replies: 8
    Last Post: 06-15-2015, 02:51 AM
  2. how can i loop this code through each row (populate and submit an HTML form)
    By GHIREM in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-19-2015, 07:39 PM
  3. Assistance needed to loop code when pulling info from a web page and pasting to excel
    By boswelljw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2015, 09:49 AM
  4. Do While Loop Code Assistance
    By cdscivic in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2014, 10:48 AM
  5. For Loop assistance
    By Paliza in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-02-2012, 05:01 AM
  6. Is it possible to populate a cell with a hyperlink but only show a shorter code?
    By Syzaghi in forum Excel General
    Replies: 3
    Last Post: 02-20-2012, 09:45 AM
  7. Assistance with Loop
    By mauddib13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2008, 06:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1