VBA code to change column data

[Samarth1508]

Hi All, 

I am trying below code to change value of another column but getting error as type mismatch.

Sub Update_Data()

Dim lr As Long

lr = Sheet2.Range("J" & Rows.Count).End(xlUp).Row

If Not IsEmpty(Sheet2.Range("J2:J" & lr).Value) Then Sheet2.Range("I2:I" & lr).Value =IIf(Left(Sheet2.Range("J2:J" & lr).Value, 4) = "SBIN", "IFT", "NEFT")

End Sub

[#2]

@Samarth1508 

excel macro to change values in a column

Sub Update_Data()
Dim lr As Long
Dim i As Long lr = Sheet2.Range(“J” & Rows.Count).End(xlUp).Row For i = 2 To lr
If Not IsEmpty(Sheet2.Cells(i, “J”).Value) Then
Sheet2.Cells(i, “I”).Value = IIf(Left(Sheet2.Cells(i, “J”).Value, 4) = “SBIN”, “IFT”, “NEFT”)
End If
Next i
End Sub

Additional link/info:

Range.Columns property (Excel)

[#3]

@Samarth1508 

Sheet2.Range("J2:J" & lr).Value is not a single value but an array of values, so you have to loop, or write a formula to the cells. For example:

Sub Update_Data()
    Dim lr As Long
    Dim r As Long
    lr = Sheet2.Range("J" & Rows.Count).End(xlUp).Row
    For r = 2 To lr
        If Sheet2.Range("J" & r).Value <> "" Then
            Sheet2.Range("I" & r).Value = _
                IIf(Left(Sheet2.Range("J" & r).Value, 4) = "SBIN", "IFT", "NEFT")
        End If
    Next r
End Sub

[#4]

Thank You so much.

Code is working perfect.

[#5]

in addition of this code I am using below code also to add unique reference number in Column (I am using Date as reference number) but I want unique reference number in each row.

can you please help me with rectification.

Sub Import_Data()

Dim lr As Long
Dim r As Long

lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
If Not IsEmpty(Sheet1.Range("A2:A" & lr).Value) Then Sheet2.Range("A2").Value = Format(Date, "DDMMYYYY")

lr = Sheet1.Range("G" & Rows.Count).End(xlUp).Row
For r = 2 To lr
If Sheet1.Range("G" & r).Value <> "" Then Sheet2.Range("I" & r).Value = IIf(Left(Sheet2.Range("J" & r).Value, 4) = "SBIN", "IFT", "NEFT")

Next r

End Sub

[#6]

@Samarth1508 

So what would you like to use as unique reference? The date plus a sequence number, or just a sequence number, or ...?

[#7]

Date + Sequence Number

[#8 · Accepted solution]

@Samarth1508 

For example:

Sub Import_Data()
    Dim lr As Long
    Dim r As Long
    Dim n As Long
    lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To lr
        If Sheet1.Range("A" & r).Value <> "" Then
            n = n + 1
            Sheet2.Range("A2").Value = Format(Date, "DDMMYYYY") & "_" & Format(n, "000")
        End If
    Next r
    lr = Sheet1.Range("G" & Rows.Count).End(xlUp).Row
    For r = 2 To lr
        If Sheet1.Range("G" & r).Value <> "" Then
            Sheet2.Range("I" & r).Value = _
                IIf(Left(Sheet2.Range("J" & r).Value, 4) = "SBIN", "IFT", "NEFT")
        End If
    Next r
End Sub

(I would prefer to use YYYYMMDD as date format since that makes it easier to sort the rows if necessary)

[#9]

Code is working perfect now.

Thank you so much for your support and Time