VBA help

[Alisya]

Hi,

I am after a VBA code that will look in Col B5:B, if "N/A" found then copy the the a/c in Col C and place under the existing list in sheet "Assumptions" col C19:C and then let the user know via a msg box the new a/c ????? has been added to Assumptions.

So for the below example CITI.OPSMFI would be added to the list.

Excel Workbook
	B	C
5	Rec	Account
6	CHESS	CHES.0014074091
7	CITIINTL	CHES.0014074074
8	CITIBANK	CHES.0000927911
9	SMP	CITI.WTISII
10	LIFE EXIGO	CHES.0013667519
11	AMPLIFE INTL	CHES.0014074104
12	NOMINEE EXIGO	CHES.0014074082
13	N/A	CITI.OPSMFI
14	NZACLEAR	CITI.ICWMAM
15
16
Sheet1

 

[kpark91]

Try

Sub kpark91()
    Dim LR1 As Long, i As Long, LR2 As Long, count As Long
    LR1 = Range("A" & Rows.count).End(xlUp).Row
    LR2 = Worksheets("Assumptions").Range("A" & Rows.count).End(xlUp).Row
    If LR1 < 4 Then Exit Sub
    If LR2 < 18 Then Exit Sub
    count = LR2 + 1
    For i = 5 To LR1
        If Ucase$(Trim$(Range("B" & i).Value)) = "N/A" Then
            Worksheets("Assumptions").Range("C" & count).Value = Range("C" & i).Value
        End If
    Next i
End Sub

 

[Alisya]

I tried the code but nothing happened. Also i need the msg box to flag the user which new a/c has been added to assumptions

 

[Alisya]

Ok, i changed this to C as there is no data in A, then the code worked. Just need the msg box.

LR1 = Range("C" & Rows.count).End(xlUp).Row

 

[kpark91]

Where have you copy+pasted this code and were there anything on Assumptions worksheet?

Sub kpark91()
    Dim LR1 As Long, i As Long, LR2 As Long, count As Long, sw As Boolean
    sw = False
    LR1 = Range("C" & Rows.count).End(xlUp).Row
    LR2 = Worksheets("Assumptions").Range("C" & Rows.count).End(xlUp).Row
    If LR1 < 5 Then LR1 = 5
    If LR2 < 19 Then LR2 = 18
    count = LR2 + 1
    For i = 5 To LR1
        If Ucase$(Trim$(Range("B" & i).Value)) = "N/A" Then
            Worksheets("Assumptions").Range("C" & count).Value = Range("C" & i).Value
            sw = True
        End If
    Next i
    If sw = True Then MsgBox "New account has been added to assumptions"
End Sub

btw, are you sure you want the msgboxes to list ALL the a/c that have been added?
There might be too many to fit.

 

[kpark91]

Ok, i changed this to C as there is no data in A, then the code worked. Just need the msg box.

LR1 = Range("C" & Rows.count).End(xlUp).Row

Good call.

 

[Alisya]

Hi, your code fails if i have large data set i get type mismatch on this line

If Ucase$(Trim$(Range("B" & i).Value)) = "N/A" Then

Where have you copy+pasted this code and were there anything on Assumptions worksheet?

Sub kpark91()
    Dim LR1 As Long, i As Long, LR2 As Long, count As Long, sw As Boolean
    sw = False
    LR1 = Range("C" & Rows.count).End(xlUp).Row
    LR2 = Worksheets("Assumptions").Range("C" & Rows.count).End(xlUp).Row
    If LR1 < 5 Then LR1 = 5
    If LR2 < 19 Then LR2 = 18
    count = LR2 + 1
    For i = 5 To LR1
        If Ucase$(Trim$(Range("B" & i).Value)) = "N/A" Then
            Worksheets("Assumptions").Range("C" & count).Value = Range("C" & i).Value
            sw = True
        End If
    Next i
    If sw = True Then MsgBox "New account has been added to assumptions"
End Sub

btw, are you sure you want the msgboxes to list ALL the a/c that have been added?
There might be too many to fit.

 

[Alisya]

There is # before N, maybe that is causing it?

#N/A

 

[Alisya]

Anyone? It appears the # is causing the issue, how can this be resolved as there is always # when there is N/A.

 

[kpark91]

#N/A occurs when there is an error.
http://office.microsoft.com/en-us/excel-help/correct-a-n-a-error-HP005203936.aspx

It is best to get rid of the error.
May I ask which formula you are using, perhaps there is a missing reference or something.