If in column K if there is an error then copy the column L data add 00 in it and paste the same
here in column K , K7 has a error,so we will look column L (L7) so L7 has 168 so as per condition we will add 00 in it so the result will be 16800
so in K7 there will be 16800
highlighted colour is only for understanding purpose
all files are located in a different path
macro will be placed in a macro.xlsm
and this data which is present in the sample file will be named as 1.xls
sheet name can be anything
copy paste if error found
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
copy paste if error found
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: copy paste if error found
Welcome to Eileen's Lounge!
I will help you with this one. Please study the code and learn from it!
You can modify the code to suit your preferences.
I will help you with this one. Please study the code and learn from it!
You can modify the code to suit your preferences.
Code: Select all
Sub Repair()
Dim fil As String
Dim wbk As Workbook
Dim wsh As Worksheet
Dim r As Long
Dim m As Long
' Prompt user for workbook
fil = Application.GetOpenFilename("Excel Workbooks (*.xls),*.xls")
If fil = "False" Then
Beep
Exit Sub
End If
Application.ScreenUpdating = False
' Open the workbook
Set wbk = Workbooks.Open(fil)
Set wsh = wbk.Worksheets(1)
' Loop through the cells in column K
m = wsh.Range("K" & wsh.Rows.Count).End(xlUp).Row
For r = 2 To m
If IsError(wsh.Range("K" & r).Value) Then
wsh.Range("K" & r).Value = 100 * wsh.Range("L" & r).Value
End If
Next r
' Optional: close and save the workbook
wbk.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: copy paste if error found
Thnx Alot HansV Sir for helping me in solving this problem Sir
Have a Great Day
Have a Great Day