vba for a range and return value

[Sotos13]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]YES[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]YES[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=A1+1[/TD]
[TD]YES[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NO[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NO[/TD]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]YES[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks
****** id="cke_pastebin" style="position: absolute; top: 110.4px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]YES[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[offthelip]

I presumed you want the values to be put in A1 for every row in B and C, so try this:

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]

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]

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]

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]

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:

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]

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]

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]

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

 

[offthelip]

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