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>
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,032
Office Version
2010
Platform
Windows
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
Joined
Mar 8, 2019
Messages
42
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
Joined
Dec 23, 2017
Messages
1,032
Office Version
2010
Platform
Windows
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
Joined
Mar 8, 2019
Messages
42
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
Joined
Dec 23, 2017
Messages
1,032
Office Version
2010
Platform
Windows
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
Joined
Dec 23, 2017
Messages
1,032
Office Version
2010
Platform
Windows
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
Joined
Mar 8, 2019
Messages
42
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
Joined
Mar 8, 2019
Messages
42
nameManager is one cell Namemager1 is 10 cells in a row for example F1:P1
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,032
Office Version
2010
Platform
Windows
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
 

Forum statistics

Threads
1,085,314
Messages
5,382,923
Members
401,809
Latest member
jbeanx80

Some videos you may like

This Week's Hot Topics

Top