vba for a range and return value

Sotos13

New Member
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
ABCDEF
5YES5
YES10
=A1+1YES12
15
NO20
NO22
25
YES30
32

<tbody>
</tbody>


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

<tbody>
</tbody>
</body>
 

offthelip

Well-known Member
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
 

Sotos13

New Member
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
 

offthelip

Well-known Member
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?
 

Sotos13

New Member
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
 

offthelip

Well-known Member
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
 

offthelip

Well-known Member
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?
 

Sotos13

New Member
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
 

Sotos13

New Member
nameManager is one cell Namemager1 is 10 cells in a row for example F1:p1
 

offthelip

Well-known Member
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
 

Some videos you may like

This Week's Hot Topics

Top