Storing the Cell Reference in a Variable for further use

[AsoInfo]

Hi there,

I have used C++ and Java but never used VBA before so maybe my question is a bit naive.

This is how my data looks like:

A B B 1 36
B C FALSE 2 6

First two columns are names, third column is the comparison between Column 1 and Column 2, 4th and 5th columns are the values of 1st and 2nd column respectively.

I want to write a code such that

1) If column 3 is not false (for example B).
2) Then store the value of respective row of column 5 (e.g. 36)
3) Search "B" in column A.
4) Store the value of column 4 (e.g 2)
5) Subtract both values of column of 5 and column 4.
6) Write this new value in a new column. (34)

So far my code looks like:

Sub Comparison()
Dim Counter As Integer
Dim F1 As Integer
Dim C1 As String
For Counter = 1 To 2
If (Worksheets("Sheet2").Cells(Counter, 3).Value <> False) Then
C1 = Worksheets("Sheet2").Cells(Counter, 3).Value
F1 = Worksheets("Sheet2").Cells(Counter, 5).Value
End If
Next Counter
End Sub


I got stuck to search a value in a specific range and to have the related value of column 4 in a variable.

Thank you for your support!

 

[SteveO59L]

Regarding step 3), will there be more than 1 "B" value?

 

[AsoInfo]

No, there is only one B value

 

[SteveO59L]

something along the lines of

Dim val1 As Integer
Dim c As Range, rng
Dim LASTROW As Long

LASTROW = Cells(Rows.Count, 1).End(xlUp).Row

Set rng = Range("a1:a" & LASTROW)

If Range("c1") <> "" Then
val1 = Range("e1").Value
MsgBox val1
End If
For Each c In rng
If c.Value = "B" Then
val1 = val1 - Range("D" & c.Row).Value
Range("m1") = val1
Exit Sub
End If
Next c

 

[AsoInfo]

Thank you so much for such a quick response!

Let me try it and will give feedback afterwards.

Thanks once again!

 

[AsoInfo]

Thank you once again!

Your code worked like a magic.... I used the same logic so that it can fit into my code and it worked perfectly.

Good Day!

 

[SteveO59L]

No probs, thanks for the feedback