vba for a range and return value

Sotos13

New Member
Joined
Mar 8, 2019
Messages
42
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>
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top