Forum Discussion
Samarth1508
Mar 17, 2023Copper Contributor
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
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)
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
- Samarth1508Copper Contributorin 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 SubSo what would you like to use as unique reference? The date plus a sequence number, or just a sequence number, or ...?
- Samarth1508Copper ContributorThank You so much.
Code is working perfect.
- NikolinoDEGold Contributor
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 SubAdditional link/info:
Range.Columns property (Excel)