PDA

View Full Version : Compare one column with one cel and input result on a second column



latinsbest
03-12-2013, 12:45 PM
Hello community I have searched a bit on this subject and I think I haven't used the proper wording or the question simply has not been asked yet.

I will try to lay out the situation to the best of my ability.

Column A (A2:A100) has the dates. A2 being 3/12/2013 and A100 being 6/18/2013. Column B will be the result column.

N12 Is the control number and N13 the identifier.

N12 is a number that will be entered manually on a daily basis.

N13 contains the current date *Today ()*.

I want to create a macro to then be executed with a button on this sheet. (I know how to link the macro to a button)

Macro:
I would like the macro to compare N13 to column A and whatever cell that matches the date on N13 then would copy N12 to the adjacent cell on Column B.

Example:
If I enter 50 in the control number (N12) and click the button, the macro will find the date in N13 on column A, in this example A5, and enter 50 in B5.

Edit: I got this to work but I need the macro to verify the whole column and produce the result on the adjecent column

Sheets("Control").Activate
If Sheets("control").Range("a5") = Sheets("control").Range("n13") Then
Sheets("control").Range("b5") = Sheets("control").Range("n12")
End If

enrand22
03-12-2013, 01:22 PM
check it out:

Sub latinsbest()

Dim lastrow As String
Dim bcell As Range

Sheets("Control").Activate
lastrow = Range("a1048576").End(xlUp).Row
For Each bcell In Range("a5:a" & lastrow)

If Sheets("control").Range("a" & bcell.Row) = Sheets("control").Range("n13") Then
Sheets("control").Range("b" & bcell.Row) = Sheets("control").Range("n12")
End If
next bcell

End Sub

latinsbest
03-12-2013, 01:50 PM
This worked just perfect... I appretiate your help!


check it out:

Sub latinsbest()

Dim lastrow As String
Dim bcell As Range

Sheets("Control").Activate
lastrow = Range("a1048576").End(xlUp).Row
For Each bcell In Range("a5:a" & lastrow)

If Sheets("control").Range("a" & bcell.Row) = Sheets("control").Range("n13") Then
Sheets("control").Range("b" & bcell.Row) = Sheets("control").Range("n12")
End If
next bcell

End Sub

enrand22
03-12-2013, 01:57 PM
perfect! you are welcome.

SamT
03-12-2013, 02:00 PM
Sub CompareDateToControl()
'Sheets("Control") is active, because that is where the Macro Button is
Dim lastrow As String
Dim Cel As Range
Dim ControlDate As Long
Dim ControlNumber As Long ' Assumes number in "N12" is always a Long
'Dim ControlNumber As Variant 'Allows any Type in "N12". Uses more memory and cpu time

ControlDate = CLng(CDate(Range("N13"))) 'Allows any date format in any language
ControlNumber = Range("N12")
lastrow = Range("a1048576").End(xlUp).Row 'Limited to newer Excel versions

For Each Cel In Range("A2:A" & lastrow)

'CDate(Cel) allows for a date+time or string value in Cel
'CLng enforces data type compatiblity
If CLng(CDate(Cel)) = ControlDate Then
Cel.Offset(, 1) = ControlNumber
End If

Next bcell

End Sub

latinsbest
03-12-2013, 02:55 PM
Thanks SamT in explaining. It gave me some insight on how this works.


Sub CompareDateToControl()
'Sheets("Control") is active, because that is where the Macro Button is
Dim lastrow As String
Dim Cel As Range
Dim ControlDate As Long
Dim ControlNumber As Long ' Assumes number in "N12" is always a Long
'Dim ControlNumber As Variant 'Allows any Type in "N12". Uses more memory and cpu time

ControlDate = CLng(CDate(Range("N13"))) 'Allows any date format in any language
ControlNumber = Range("N12")
lastrow = Range("a1048576").End(xlUp).Row 'Limited to newer Excel versions

For Each Cel In Range("A2:A" & lastrow)

'CDate(Cel) allows for a date+time or string value in Cel
'CLng enforces data type compatiblity
If CLng(CDate(Cel)) = ControlDate Then
Cel.Offset(, 1) = ControlNumber
End If

Next bcell

End Sub