Home In Excel, how do you use a list value in a loop to connect the value to a worksheet name?
Reply: 0

In Excel, how do you use a list value in a loop to connect the value to a worksheet name?

user6658
1#
user6658 Published in September 20, 2018, 10:21 pm

I am attempting to create a loop but running into a slight snag that I cannot seem to figure out how to fix; hoping that someone can help me.

I have a list of names in Sheet1 Column A that I would like to use their values as the Sheet names when the loop is running.

Example:

Dim Name As String    
Names = Sheets("Sheet1").Range("A1").value  

But instead of it sticking with Range("A1").Value, I am needing it to swing down the list until it hits a blank row. I found a few different syntax/coding and tried to make it work but got stuck here.

With a list of names in Column A, the code will run directly to the bottom of the list, pull the value of the name and go directly to the that sheet.

Example:

List in Column A:

Mark
John
Jason

The code will grab Jason, go to the Jason worksheet, and enter the formula. However, it skips Mark and John. Not sure how to fix from here.

Any help would be greatly appreciated.

Sub RunTest()

       Dim i, lastcell As Long

       Dim Name As String

       lastcell = Range("A" & Cells.Rows.Count).End(xlUp).Row

       Name = Range("A" & Cells.Rows.Count).End(xlUp).Value

       Range("A1").Select

    For i = 1 To lastcell

             Sheets(Name).Select

             Range("A1").Select

             ActiveCell.Offset(0, 2).Select

             ActiveCell.FormulaR1C1 = "=2"

             Sheets("sheet1").Select

             ActiveCell.Select

            ActiveCell.Offset(1, 0).Select

    Next I

End Sub
share|improve this question
  • You don't tie Name to the current row – Tim Williams Feb 12 at 19:14

2 Answers 2

active oldest votes
up vote 1 down vote accepted
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302999 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO