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>
 
this code is shows you how to do a MATCH in VBA:
Code:
marr = Range("NameManager1")
farr = Range("Namemanager")
For kk = LBound(marr, 1) To UBound(marr, 1)
 If marr(kk, 1) = farr Then
   MsgBox kk
  Exit For
 End If
Next kk
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
so A1 is called in "Smith" and my range from F1:P1 is called Tom.
My cells in my range of C column will fill one by one "Smith" .
so i have these two formulas =MATCH(Smith;Tom;0) which is in AA20 cell and the formula =AA20+COUNTIF(Tom;Smith)-1.
so if in column B we have a Yes the in column D put the result for the match fuction and in E the result of the countif
 
Upvote 0
I think I have understood your requirements, if so this should do it:
Code:
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 3))
outarr = Range(Cells(1, 4), Cells(lastrow, 4))
tomrange = Range("Tom")


 For i = 1 To lastrow
  If inarr(i, 1) = "YES" Then
       A1 = inarr(i, 2)
       matchfnd = False
       matchi = 0
       matchcnt = 0
       For kk = LBound(tomrange, 2) To UBound(tomrange, 2)
       
        If tomrange(1, kk) = A1 Then
          If Not (matchfnd) Then
            matchfnd = True
            matchi = k
          End If
            matchcnt = matchcnt + 1
   
        End If
       Next kk
            outarr(i, 1) = matchi + matchcnt - 1
  End If
 Next i
Range(Cells(1, 4), Cells(lastrow, 4)) = outarr
 
Upvote 0
I just spotted a typo in the code "k" should be "kk"
try 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))
tomrange = Range("Tom")


 For i = 1 To lastrow
  If inarr(i, 1) = "YES" Then
       A1 = inarr(i, 2)
       matchfnd = False
       matchi = 0
       matchcnt = 0
       For kk = LBound(tomrange, 2) To UBound(tomrange, 2)
       
        If tomrange(1, kk) = A1 Then
          If Not (matchfnd) Then
            matchfnd = True
            matchi = kk
          End If
            matchcnt = matchcnt + 1
   
        End If
       Next kk
            outarr(i, 1) = matchi + matchcnt - 1
  End If
 Next i
Range(Cells(1, 4), Cells(lastrow, 4)) = outarr
 
End Sub
 
Upvote 0
Hi,
Sorry for the late response.
yes it about what i want but up till now i think the best solution was
Sub test1()


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(4, 1)
End If
Next i
Range(Cells(1, 4), Cells(lastrow, 4)) = outarr

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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