Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: VBA is it possible to use a integer like function with cells

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA is it possible to use a integer like function with cells

    hey,

    so im really new to VBA, and im not sure if this is possible.

    the thing i want to achieve is:

    in row a i have names, these are names of sheets i already have. these sheets change.
    so lets say A1 has "sheet1" in it. now what i want is that with VBA i want to make a formula that takes the text of A1 and puts it in B1 with a "=" etc.
    so the end result should be something along the line with B1= "=sheet1!A1"
    i know this can be done with:
    sProfileName = Range("a1").Value
    Range(B1).Formula = "=" & sProfileName & "!A1"

    but now i want to somewhat continue. so that it automatically does the same with A2, A3, A4 etc.
    so in A2 there's "sheet2" and the VBA puts "=sheet2!A1" in B2
    and in A3 there's "sheet3" and the VBA puts "=sheet3!A1" in B3

    i know with integer (maybe this had nothing to do with it) you can say i = 1 to 10, and the first i takes 1 second takes 2 etc. is it possible to say something along the line of integer i = range A1 to A3 and then in the first i it uses the text of A1 (so "sheet1") and the second time i gets used it takes the text of a2 (so "sheet2").

    i have no idea if this is understandable haha..
    let me know if its not,

    thanks in advance Jonah

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,513
    Post Thanks / Like
    Mentioned
    249 Post(s)
    Tagged
    17 Thread(s)

    Default Re: VBA is it possible to use a integer like function with cells

    How about
    Code:
    Sub Myloop()
       Dim Cl As Range
       
       For Each Cl In Range("A1", Range("A" & Rows.count).End(xlUp))
          Cl.Offset(, 1).Formula = "=" & Cl.Value & "!A1"
       Next Cl
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Aug 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA is it possible to use a integer like function with cells

    ur a hero, thanks alot
    i have no idea how it works, but it works

    thanks again

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,513
    Post Thanks / Like
    Mentioned
    249 Post(s)
    Tagged
    17 Thread(s)

    Default Re: VBA is it possible to use a integer like function with cells

    Glad to help & thanks for the feedback

    All it's doing is looping the col A from A1 to the last used cell in that column & adding the cell value to the formula in colB
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    New Member
    Join Date
    Aug 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA is it possible to use a integer like function with cells

    thanks again , theres only one small problem. it works great but colom a is filled with formula's in with like a if function that says if > then "sheet1"if not then """, so it works great and does it well but at the end of the macro i get an error because it cant find sheet "".

    lol this is a vague explanation.

    it works great but get an error at the end, is this fixable?

    thankssss

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,513
    Post Thanks / Like
    Mentioned
    249 Post(s)
    Tagged
    17 Thread(s)

    Default Re: VBA is it possible to use a integer like function with cells

    How about
    Code:
    Sub Myloop()
       Dim Cl As Range
       
       For Each Cl In Range("A1", Range("A" & Rows.count).End(xlUp))
          If Cl.Value = "" Then Exit For
          Cl.Offset(, 1).Formula = "=" & Cl.Value & "!A1"
       Next Cl
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,098
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 Thread(s)

    Default Re: VBA is it possible to use a integer like function with cells

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub Myloop()
       Dim Cl As Range
       
       For Each Cl In Range("A1", Range("A" & Rows.count).End(xlUp))
          If Cl.Value = "" Then Exit For
          Cl.Offset(, 1).Formula = "=" & Cl.Value & "!A1"
       Next Cl
    End Sub
    I don't think you should exit the For loop, rather, I think if the cell in Column A is displaying the empty text string (""), you should just skip over it (there might be other cells after it with displayed sheet names). Something like this for your code...
    Code:
    Sub Myloop() Dim Cl As Range For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp)) If Len(Cl.Value) Then Cl.Offset(, 1).Formula = "=" & Cl.Value & "!A1" Next Cl End Sub
    And, for those who find such things interesting, you can also do this without using a loop...
    Code:
    Sub Myloop2() With Range("A1", Range("A" & Rows.Count).End(xlUp)) .Offset(, 1).Formula = Evaluate(Replace("IF(@="""","""",""=""&@&""!A1"")", "@", .Address)) End With End Sub
    Last edited by Rick Rothstein; Aug 23rd, 2018 at 03:03 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    Aug 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA is it possible to use a integer like function with cells

    they both work, rick thanks for your response in my case its possible to end the loop.

    so both macros work perfect, thanks alot

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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