+ Reply to Thread
Results 1 to 5 of 5

Refer sheet name by For Each loop error

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-26-2013
    Location
    Makati
    MS-Off Ver
    Excel 2010
    Posts
    138

    Refer sheet name by For Each loop error

    Hi Guys,

    Can someone enlighten me for this, why is my code error when i want to put a value on the new sheet that i just add.
    The error is on the line below
    Me.Sheets(Ticker).Range("A1").Value = "SASAS"
    Runtime Error 13 , type mismatch is what show up.


    Dim StartDate As Date, EndDate As Date
    Dim WS As Worksheet
    Dim Ticker
    Dim LastRow As Integer, i As Integer
    Dim qurl As String
    
    StartDate = Me.Sheets(1).Range("B3").Value
    EndDate = Me.Sheets(1).Range("D3").Value
    LastRow = Me.Sheets(1).Range("F" & Rows.Count).End(xlUp).Row
    i = 0
    
    If LastRow = 3 Then
    MsgBox ("Please put at least 1 ticker starting from cell F4")
    Exit Sub
    End If
    
    For Each Ticker In Me.Sheets(1).Range("F4:F" & LastRow)
    
    Me.Sheets.Add(After:=Sheets(Sheets.Count)).Name = Ticker
    
    Me.Sheets(Ticker).Range("a1").Value = "SASAS"
    
    Next Ticker
    
    Exit Sub
    Thanks in advance for the help.

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

    Re: Refer sheet name by For Each loop error

    Try this...

    Me.Sheets(Ticker.Value).Range("a1").Value = "SASAS"
    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
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454

    Re: Refer sheet name by For Each loop error

    Hi,

    Are you trying to add and reference a sheet called Ticker in these lines of code?

    Me.Sheets.Add(After:=Sheets(Sheets.Count)).Name = Ticker
    
    Me.Sheets(Ticker).Range("a1").Value = "SASAS"
    If so, you need some quote marks..

    Me.Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Ticker"
    
    Me.Sheets("Ticker").Range("a1").Value = "SASAS"
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Refer sheet name by For Each loop error

    Try:

    Me.Sheets.Add(After:=Sheets(Sheets.Count)).Name = Ticker.Value
    
    Me.Sheets(Ticker.Value).Range("a1").Value = "SASAS"

  5. #5
    Forum Contributor
    Join Date
    06-26-2013
    Location
    Makati
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: Refer sheet name by For Each loop error

    Wow...can't believe i didnt try that...hahaha..Thanks Guys!!
    Works great with Ticker.value , thanks again

+ 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] refer to Textbox using a loop
    By mortphil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-18-2013, 06:55 AM
  2. Replies: 7
    Last Post: 12-16-2012, 04:24 PM
  3. Cannot refer to 'error' = #N/A cell output
    By heatwave in forum Excel General
    Replies: 4
    Last Post: 03-09-2011, 11:10 PM
  4. Loop Statement : Use of string to refer userform object
    By asha3010 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-10-2010, 02:27 PM
  5. Refer checkbox by name in a loop
    By Xtender in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2006, 07:50 AM

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