PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] Loop to paste new file location in the next cell below previous-VBForums
Results 1 to 6 of 6

Thread: [RESOLVED] Loop to paste new file location in the next cell below previous

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2018
    Posts
    16

    Resolved [RESOLVED] Loop to paste new file location in the next cell below previous

    Good Morning/Afternoon,

    Was looking for some help. I'm currently working on some coding that essentially allows me to browse a .mpp file as many times as I want until I hit cancel on the FindFile. The hyperlink to the file location saves in "B2" but I want it after the first loop to go to B3, B4 and so on until I've selected the correct amount of project files. Maybe even implementing a multiple selection from 1 findFile and have it dump each and every one of the hyperlinks into different cells in a column.

    Sub ConnectSchedule()

    Dim File As String

    File = ""

    While File = ""

    File = findFile("MS Project Schedule")

    If File = "" Then
    File = False
    MsgBox "No Schedule Selected", vbOKOnly, "Please Select a File"

    Else
    With ThisWorkbook.Sheets("TestBurndown")
    .Hyperlinks.Add .Range("B2"), File
    With .Range("C2")
    .NumberFormat = ";;;"
    .FormulaR1C1 = File
    End With
    With .Range("B2").Font
    .Size = 11
    .Bold = True
    End With
    End With
    File = ""
    End If
    Wend

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,051

    Re: Loop to paste new file location in the next cell below previous

    Firstly, please use appropriate formatting tags when posting code snippets. Doing so maintains indenting and thus makes code far more readable. That said, it helps if code is properly indented to begin with. I have fixed the formatting in your code below.
    vb.net Code:
    1. Sub ConnectSchedule()
    2.     Dim File As String
    3.  
    4.     File = ""
    5.  
    6.     While File = ""
    7.         File = findFile("MS Project Schedule")
    8.  
    9.         If File = "" Then
    10.             File = False
    11.             MsgBox "No Schedule Selected", vbOKOnly, "Please Select a File"
    12.         Else
    13.             With ThisWorkbook.Sheets("TestBurndown")
    14.                  .Hyperlinks.Add .Range("B2"), File
    15.  
    16.                  With .Range("C2")
    17.                     .NumberFormat = ";;;"
    18.                     .FormulaR1C1 = File
    19.                  End With
    20.  
    21.                  With .Range("B2").Font
    22.                     .Size = 11
    23.                     .Bold = True
    24.                  End With
    25.             End With
    26.  
    27.             File = ""
    28.         End If
    29.     End While
    There was some weirdness in your code too though, only some of which I could fix.

    As for the question, you would use a loop and increment the row number each time, e.g.
    vb.net Code:
    1. Dim rowNumber = 2
    2.  
    3. While flag
    4.     Dim rangeName = "B" & rowNumber
    5.  
    6.     '...
    7.  
    8.     rowNumber += 1
    9. End While
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,051

    Re: Loop to paste new file location in the next cell below previous

    Actually, I just realised that that isn't even VB.NET code. That must be either VB6 or maybe VBA. Please identify what language you're actually using and then use the Report Post icon below your post to ask the mods to move this thread to the appropriate forum.

    The answer I provided still applies though, in principle at least, even if the code must be changed slightly.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jul 2018
    Posts
    16

    Re: Loop to paste new file location in the next cell below previous

    Sorry about that jmcilhinney.

    I've reported the post to get it moved to the office applications side where I see most VBA code posted. I appreciate the help. Just one question, how do I keep the formatting of when I post in the code? I've been having issues trying to figure out how to do that.

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,051

    Re: Loop to paste new file location in the next cell below previous

    Quote Originally Posted by benrbeau View Post
    Just one question, how do I keep the formatting of when I post in the code?
    The last two buttons on the editor toolbar provide code formatting tags, one with syntax highlighting and one without. You can experiment with them in the Forum Test Area forum.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,214

    Re: Loop to paste new file location in the next cell below previous

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width