Forum Discussion

Copper Contributor

Mar 17, 2023

Solved

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 ...

Mar 17, 2023

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

Copper Contributor

Mar 17, 2023

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

MVP

Mar 17, 2023

Samarth1508

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

Samarth1508

Copper Contributor

Mar 17, 2023

Date + Sequence Number

HansVogelaar

MVP

Mar 17, 2023

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
Copper Contributor
Mar 17, 2023
Code is working perfect now.

Thank you so much for your support and Time 😊😍

Forum Discussion

VBA code to change column data

Share

Resources