+ Reply to Thread
Results 1 to 8 of 8

First empty column and then run range

  1. #1
    Registered User
    Join Date
    10-10-2019
    Location
    Sweden
    MS-Off Ver
    360
    Posts
    18

    First empty column and then run range

    Hi!

    I'm using this script:
    Sub GoGo()

    ' Finds first blank column
    NewCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column + 1

    ' Puts your header at the top of the first blank column
    Cells(1, NewCol).Value = "WeekNumber"

    ' Works out the first blank row under the new header
    lastrow = ActiveSheet.Cells(Rows.Count, NewCol).End(xlUp).Row + 1

    ' Updates the first blank row of the first blank column with your desired formula
    Cells(lastrow, NewCol).Formula = "=VLOOKUP(A1:A52,Blad2!$A:$C,3,0)"

    End Sub

    With this script I will only get results in the first row. But I would like this to run for a set amount of rows. Lets say 53 as in the example file.

    // 2times
    Attached Files Attached Files
    Last edited by 2times; 11-19-2019 at 08:37 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: First empty column and then run range

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    10-10-2019
    Location
    Sweden
    MS-Off Ver
    360
    Posts
    18

    Re: First empty column and then run range

    Nice

    Thank you!

    Do you now if there is a way to paste the values instead of the formula?
    I will change the data on sheet 2 (Blad2) every week.

    And also, return a blank cell instead of FALSE .

    2times
    Last edited by 2times; 11-19-2019 at 08:58 AM.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: First empty column and then run range

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-10-2019
    Location
    Sweden
    MS-Off Ver
    360
    Posts
    18

    Re: First empty column and then run range

    AlphaFrog!! You are the best!

    Thank you so much!

  6. #6
    Registered User
    Join Date
    10-10-2019
    Location
    Sweden
    MS-Off Ver
    360
    Posts
    18

    Re: First empty column and then run range

    AlphaFrog!

    Tried to send you a private message but looks like it didn't go through.
    So Im posting the same thing here.

    I'm trying to modify this:
    "=IFERROR(VLOOKUP($A2,Blad2!$A:$C,3,0),"""")"

    To this:
    "=IFERROR(VLOOKUP("*"&$A2&"*",Blad2!$A:$C,3,0),"""")"
    But I’m getting an error. Do you know why?

    After this I will try to not disturb you again 😊

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: First empty column and then run range

    No need to PM me. Please stop. I'm automatically notified when you post to this thread. That how it works.

    The formula in VBA is a string that begins and ends with a quote. For each literal quote within the string, use two consecutive quotes.

    "=IFERROR(VLOOKUP(""*""&$A2&""*"",Blad2!$A:$C,3,0),"""")"
    Last edited by AlphaFrog; 11-20-2019 at 07:11 AM.

  8. #8
    Registered User
    Join Date
    10-10-2019
    Location
    Sweden
    MS-Off Ver
    360
    Posts
    18

    Re: First empty column and then run range

    So sorry, won't happen again.
    But thank you for helping me again. That did the trick!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Using macro to find first empty cell in column. If range is empty returns error.
    By A440 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-23-2019, 11:03 AM
  2. sum a range of cells from empty to empty cell in a big table
    By Ram71 in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 03-09-2018, 03:12 PM
  3. [SOLVED] How to create a dynamic range if the last cell is empty and the columns have empty cells
    By peter_swe in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2016, 11:18 PM
  4. Replies: 0
    Last Post: 08-03-2016, 07:01 PM
  5. Replies: 2
    Last Post: 07-21-2016, 09:40 PM
  6. MACRO to empty a range of cells based on whether specified cell is empty
    By TBJV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 10:35 PM
  7. Finding next empty empty cell in a range of columns
    By UncleBun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2006, 07: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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1