Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: vba for a range and return value

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vba for a range and return value

    Hi,

    i want to create a vba where i have a cell which when i put a value from a range it brings me back value in a cell that has a formula if from another range it yes . if it's no or blank don't bring anything. here is the example. Run the code and in column D if in B1 is Yes then put the value from C1 in A1 and return in D1 the result of the formula in the cell A3. Do that for all the range in C. If in B column the cell is blank or No dont return a value
    A B C D E F
    5 YES 5
    YES 10
    =A1+1 YES 12
    15
    NO 20
    NO 22
    25
    YES 30
    32


    Thanks
    ****** id="cke_pastebin" style="position: absolute; top: 110.4px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
    YES

  2. #2
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    898
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: vba for a range and return value

    I presumed you want the values to be put in A1 for every row in B and C, so try this:
    Code:
    Sub test()
    lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    inarr = Range(Cells(1, 2), Cells(lastrow, 3))
     For i = 1 To lastrow
      If inarr(i, 1) = "YES" Then
          Cells(1, 1) = inarr(i, 2)
          Cells(i, 4) = Cells(3, 1)
      End If
     Next i
     
    End Sub
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba for a range and return value

    hi
    i actually want the code to bring me back in column D next to each cell the value from A3. A3 is a formula
    i run your code but it doen't change anything.
    Thanks

  4. #4
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    898
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: vba for a range and return value

    The code is very simple and should do what you asked, have you checked how "YES" is spelt do you have it all in UPPER CASE?
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  5. #5
    New Member
    Join Date
    Mar 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba for a range and return value

    yes..i was about to reply ypu that it works
    thank you very much.
    i have two questions...can i have a named range from name manager
    and i think it runs a little slow..it fllls two lines in 1 second

  6. #6
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    898
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: vba for a range and return value

    The reason the code is a bit slow is that everytime VBA writes to the cell A3 it will force a recalculation, the code could be speeded up a bit by writing the column D in one go by using a variant array: like this:
    Code:
    Sub test()
    lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    inarr = Range(Cells(1, 2), Cells(lastrow, 3))
    outarr = Range(Cells(1, 4), Cells(lastrow, 4))
     For i = 1 To lastrow
      If inarr(i, 1) = "YES" Then
          Cells(1, 1) = inarr(i, 2)
          outarr(i, 1) = Cells(3, 1)
      End If
     Next i
    Range(Cells(1, 4), Cells(lastrow, 4)) = outarr
     
    End Sub
    However the way to really speed up the code is to do the calcualtion which is in A3 in VBA. Is your formula really just =A1+1 because if this is the case this can be done directly in VBA
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  7. #7
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    898
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: vba for a range and return value

    I forgot to answer your question about using a named range, yes you can use a named range in the code but it will need to ber changed, What columns does your named range cover?
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  8. #8
    New Member
    Join Date
    Mar 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba for a range and return value

    Good idea
    this is one formula =MATCH(NameManager;NameManager1;0) and the other one is D1+COUNTIF(NameManager1;NameManager)-1.
    D1 is the Match formula.
    so i basically wanted to fill in two columns. in d column the result of match formula and in E column the result of the other formula

  9. #9
    New Member
    Join Date
    Mar 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba for a range and return value

    nameManager is one cell Namemager1 is 10 cells in a row for example F1:P1

  10. #10
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    898
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: vba for a range and return value

    Both of your equations are invalid in my version of EXCEL (2007) so I don't know what you are trying to do, so can you explain in words,
    The functionality of the MATCH function can easily be done in VBA , ditto the countif fumction
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

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
  •