Hi Experts,
I am looking for a macro that will do the things mentioned below
there are 2 files 1.xls & macro.xlsm (macro will be placed in macro.xlsm),both files are located in different places so the path will be hardcoded in the macro so that i can change it as per my needs
sheet name can be anything
plz see the sample file
Condition: If column D of 1.xls matches with column E of 1.xls Or If column D of 1.xls matches with column F of 1.xls then match column I of 1.xls with column B of Macro.xlsm & if it matches then clear all the data in that row from column C
Or
If column D of 1.xls does not matches with column E of 1.xls & with Column F of 1.xls too.. then put the remarks in series (numerical series)
Thnx For the Help
Clear the data Else put numbers in series by vba
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Clear the data Else put numbers in series by vba
You do not have the required permissions to view the files attached to this post.
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Clear the data Else put numbers in series by vba
Code: Select all
Sub Macro()
Dim wb As Workbook, v As Variant
Set wb = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
v = wb.Worksheets(1).Cells(1, 1).CurrentRegion
Dim i As Long, c As Range
With ThisWorkbook.Worksheets(1)
For i = LBound(v) + 1 To UBound(v)
Set c = .Columns(1).Find(v(i, 2))
If Not c Is Nothing Then
If v(i, 4) = v(i, 5) And v(i, 8) < v(i, 11) Or v(i, 4) = v(i, 6) And v(i, 8) > v(i, 11) Then
c.Offset(, 2).Resize(, c.End(xlToRight).Column - 2).ClearContents
Else
c.End(xlToRight).Offset(, 1).Value = c.End(xlToRight).Value + 1
End If
End If
Next i
End With
End Sub
I tired this but its not that much Perfect HansV Sir
Plz have a rellok Sir
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Clear the data Else put numbers in series by vba
What is the problem with the code?
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Clear the data Else put numbers in series by vba
I dont know but it has issues & some modification is also there in the macro
I have many macros so i tried to make a macro that will work
I have many macros so i tried to make a macro that will work
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Clear the data Else put numbers in series by vba
You write "it has issues". What are those issues?
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Clear the data Else put numbers in series by vba
I have not refered the columns correctly & conditions correctly I think so
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Clear the data Else put numbers in series by vba
Then edit the code so that it refers to the correct columns.
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Clear the data Else put numbers in series by vba
Sir I am unable to write the perfect code for the same
Bcoz I don't know much about VBA
Bcoz I don't know much about VBA
-
- Administrator
- Posts: 7210
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Clear the data Else put numbers in series by vba
Then why are you expecting someone else to do your work for you?
And if you "don't know much about VBA", why are you trying to create 'perfect' macros when you can't even be bothered to learn?
You have been warned enough times - either make an effort to learn or go somewhere else to have your work done for you.
Leif