Forum Discussion

Samarth1508's avatar
Samarth1508
Copper Contributor
Mar 17, 2023

VBA code to change column data

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

 

 

 

  • 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)

  • 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
    • Samarth1508's avatar
      Samarth1508
      Copper Contributor
      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
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        Samarth1508 

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

Resources