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]

this code is shows you how to do a MATCH in VBA:

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

 

[Sotos13]

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

 

[offthelip]

I think I have understood your requirements, if so this should do it:

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

 

[offthelip]

I just spotted a typo in the code "k" should be "kk"
try 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))
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

 

[Sotos13]

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