vba Copy Paste Conditional to put remark 1 2 3 .. etc

[fixer]

This post is a … Moderator Post Copy…. ( link to post is https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc#post12828 or https://tinyurl.com/y3c7lvlh
https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc#post12828

Currently the OP, ( possible real name most of the time , Avinash Singh from Mumbai India ), appears to have many of his own accounts ( hundreds by his own admission to me privately ) . In addition he has temporary access to many other peoples accounts.
It appears that his current “job” is to paste a question into a forum, and pass any macro given on to the person assigning him the task old posting the question.
He will often duplicate cross post a question.
Quite often the question is wrong and the macros and files do not match up or are full of mistakes )
( He may have his own career plans in parallel to this current job. That is likely also to be responsible for some of his many duplicate cross postings of similar questions )
He has a very poor understanding of the English language, and has never been anywhere near Excel or Microsoft Office. If the assigning person is not happy with any returned macro, then he may use some of his many canned replies or even attempt some reply himself in the hope of it generating another macro, which he passes on to the person assigning him the job of posting the question…
He barely understand , if at all, anything he posts himself in a question of the replies he makes himself
_.... If the assigning person is not happy with any returned macro, then he may use some of his many canned replies or even attempt some reply himself in the hope of it generating another macro, which he passes on to the person assigning him the job of posting the question…
He barely understand , if at all, anything he posts himself in a question of the replies he makes himself
_..... If the assigning person is not happy with any returned macro, then he may use some of his many canned replies or even attempt some reply himself in the hope of it generating another macro, which he passes on to the person assigning him the job of posting the question…
He barely understand , if at all, anything he posts himself in a question of the replies he makes himself
_.... If the assigning person is not happy with any returned macro, then he may use some of his many canned replies or even attempt some reply himself in the hope of it generating another macro, which he passes on to the person assigning him the job of posting the question…
He barely understand , if at all, anything he posts himself in a question of the replies he makes himself

_... and so on, and so on…

The questions are often variations on a similar one. In the next copy of the first Thread post, I am attempting to collect / merge / reference postings of those which can approximately be given the title of …

vba Copy Paste Conditional to put remark 1 2 3 .. etc





https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ)
https://www.youtube.com/watch?v=jdPeMPT98QU (https://www.youtube.com/watch?v=jdPeMPT98QU)
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg (https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5 (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl )
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2 (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[fixer]

Plz see the sample file
If column I is sell then see the value of column K & if column K is Greater than sheet2 of column E then put the remark in sheet3 in the stock name from column B
If column I is buy then see the value of column K & if column K is lower than sheet2 of column F then put the remark in sheet3 in the stock name from column B
remark will be in series like 1,2,3,4,5,6 and so on
vba is palced in a separate file
all files are located in same place
and after putting the remark clear sheet 1 and sheet 2


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_- (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_-)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7EqbG23kg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7EqbG23kg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7KvJXmK 8o (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7KvJXmK 8o)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7E1gwg4Aq (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7E1gwg4Aq)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79hNGvJ bu (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79hNGvJ bu)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79YAfa2 4T (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79YAfa2 4T)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79M1SYH 1E (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79M1SYH 1E)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78SxhXT nR (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78SxhXT nR)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[fixer]

This post is a … Moderator Post Copy….


vba Copy Paste Conditional to put remark 1 2 3 .. etc

https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=14765&viewfull=1#post14765
If column I is sell then see the value of column K & if column K is Greater than sheet2 of column E then put the remark in sheet3 in the stock name from column B
If column I is buy then see the value of column K & if column K is lower than sheet2 of column F then put the remark in sheet3 in the stock name from column B
remark will be in series like 1,2,3,4,5,6 and so on
vba is palced in a separate file
all files are located in same place
and after putting the remark clear sheet 1 and sheet 2


https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=13161&viewfull=1#post13161
If column B of 2.xlsm match with column B of 1.xls then paste the data from column C of 2.xls as 1,2,3,4,5 and so on....
&
If column B of 2.xlsm doesn't match with column B of 1.xls then delete all the data from column C of that row
macro will be placed in 2.xlsm
all files re located in different path
sheet name can be anything
plz see the sample file


https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=13177&viewfull=1#post13177
If column B of 2.xlsm match with column B of 1.xls then clear all the data in that row from column C
If column B of 2.xlsm does not match with column B of 1.xls then paste the data from column C of 2.xls as 1,2,3,4,5 and so on…


https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=13414&viewfull=1#post13414
All files are located in different path
sheet name can be anything
I have a file ap.xls & Book1.xlsm
macro will be placed in Book1.xlsm
We have to look on Column S of ap.xls and If column S of ap.xls has negative numbers then we have to look on Column E of ap.xls & we will match that Column E data of ap.xls with column A of sheet1 of Book1.xlsm & if it matches then we will see wheather column C of Book1.xlsm has data in it or not & if column C of Book1.xlsm has data in it then do nothing & if column C of Book1.xlsm doesn't have data then we will go to sheet3 of Book1.xlsm and we will look for a match of Column E data of ap.xls with column A of sheet3 of Book1.xlsm & if it is found then we will copy the data from sheet3 of Book1.xlsm and paste it to sheet1 of book1.xlsm & we increase one more number in series in it


https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=14138&viewfull=1#post14138
https://www.excelforum.com/excel-programming-vba-macros/1319768-if-condition-met-then-put-the-remark-between-files.html
Hi there, I am looking for a macro As mentioned below
Plz see the sample file attached below
If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is not equal to column E of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is equal to column E of 1.xls then don't do anything
Plz note: Macro will be placed in macro.xlsm which i have attached below & 1.xls & macro.xlsm may be in different path so I will edit the path As per my needs


https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=14742&viewfull=1#post14742
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)[/I]
https://www.mrexcel.com/board/threads/clear-the-data-else-put-numbers-in-series-by-vba.1141539/
https://www.excelguru.ca/forums/showthread.php?10748-Clear-the-data-Else-put-numbers-in-series
https://www.myonlinetraininghub.com/excel-forum/vba-macros/clear-the-data-else-put-numbers-in-series
https://www.experts-exchange.com/questions/29189773/Condition-Met-then-Remarks-in-Numerical-Series-by-vba.html
https://www.excelforum.com/excel-programming-vba-macros/1323098-condition-met-then-remarks-in-numerical-series-by-vba.html#post5371511 ( Closed by Jeff in error )
http://www.eileenslounge.com/viewtopic.php?f=30&t=35053


https://www.excelforum.com/excel-programming-vba-macros/1322316-condition-matches-then-clear-the-data-else-put-the-remark-in-series-by-vba.html#post5370608
there are 2 files 1.xls & macro.xlsm (macro will be placed in macro.xlsm),both files are loacted in diffrent 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 equal to column E of 1.xls & column H of 1.xls is lower than column K then match column I of 1.xls with column B of macro.xlsm & if it matches then clear the data in that row ELSE put the remark
If column D of 1.xls equal to column F of 1.xls & column H of 1.xls is greater than column K then match column I of 1.xls with column B of macro.xlsm & if it matches then clear the data in that row ELSE put the remark



https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=14745&viewfull=1#post14745
https://www.excelforum.com/excel-programming-vba-macros/1323944-conditon-met-then-paste-the-data.html
If column B of 1.xls matches with column A of Book1.xlsb & the difference between Column D of 1.xls & Column E of 1.xls is not greater than 0.50% of column D of 1.xls then put Remarks Else Clear the data from Column B of book1.xlsb
Note:
Macro will be placed in a book1.xlsb
Sheet name can be anything
Remarks means If condition met then finding the last row of book1.xlsb and add +1 to the data in the last row & put th result in next cell in that row
clear data means if condition doesnt met then clear all the data in book1.xlsb in that row
Plz see the output (it is the output of the macro)


https://www.excelforum.com/excel-programming-vba-macros/1325623-condition-met-then-clear-the-data-else-put-data-in-series.html#post5386026
If column B of alert..csv matches with column B of sample2.xlsb then clear all the data from column C
If column B of alert..csv doesn't matches with column B of sample2.xlsb then put the remarks in series by vba as mentioned in the sample file


https://www.excelforum.com/excel-programming-vba-macros/1326195-match-data-between-sheet-and-pasting-the-output-by-adding-1-a.html
https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=14879&viewfull=1#post14879
If column E of sheet3 of 3.xlsm matches with sheet4 of Column B of 10.xlsx then match the same with column B of sheet1 of 3.xlsm & if matches then put the numerical data in series by vba in sheet1 of 3.xlsm
Macro will be placed in 3.xlsm file

[Molly Brennholz]

Hello

Your data ????? :confused:
SELL is K=1100.947 , sheet 2 column E is 1102 … so K is NOT > column E ---- so no output – no remark
BUY is K = 130.734 , sheet 2 column E is 129 … so K is NOT < column E ----- so no output – no remark

See here: http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=12837&viewfull=1#post12837



So Try this data : http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=12838&viewfull=1#post12838



( You also need Function CL( ) )


To explain the solution to you:
If your data is
_____ Workbook: Merge2.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1Symbol


2ACC
1
2
3


3ADANIENT
1


4
Worksheet: Sheet3

arrS3() =

arrS3(1)arrS3(2)arrS3(3)


arrS3(1) =

Symbol


arrS3(2)=

ACC
1
2
3


arrS3(3)=

ADANIENT
1


Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) =




File here: http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=12840&viewfull=1#post12840
(You also need Function CL( ) )



Molly

[fixer]

Option Explicit
Sub STEP7() '
Rem 1 Worksheets info
Dim Wbm As Workbook, Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
Set Wbm = Workbooks.Open(ThisWorkbook.Path & "\Merge.xlsx")
Set Ws1 = Wbm.Worksheets("Sheet1"): Set Ws2 = Wbm.Worksheets("Sheet2"): Set Ws3 = Wbm.Worksheets("Sheet3")
Rem 2 data Input
Dim arrS1() As Variant, arrS2() As Variant, arrS3() As Variant
Let arrS1() = Ws1.Range("A1").CurrentRegion.Value: arrS2() = Ws2.Range("A1").CurrentRegion.Value
'2b
ReDim arrS3(1 To UBound(arrS1(), 1)) ' A 1 dimension array of arrays
''2b(i)
' Let arrS3(1) = Ws3.Range("A" & Ws3.Range("A1").CurrentRegion.Columns.Count & "") ' header row as a one dimensional array
''2b(ii) data rows array output

Rem 3
Dim cnt
For cnt = 2 To UBound(arrS1(), 1) ' "row" count, cnt
'2b)(ii)
Dim Lc As Long: Let Lc = Ws3.Cells.Item(cnt, Ws3.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
Let arrS3(cnt) = Ws3.Range("A" & cnt & ":" & CL(Lc + 1) & cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays
Select Case arrS1(cnt, 9) ' column I
Case "SELL" 'If column I is sell
If arrS1(cnt, 11) > arrS2(cnt, 5) Then ' if column K is Greater than sheet2 of column E then
Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) = UBound(arrS3(cnt), 2) - 1 ' Put in a value in last array "column"
Else
End If
Case "BUY" 'If column I is buy
If arrS1(cnt, 11) < arrS2(cnt, 6) Then ' if column K is lower than sheet2 of column F then
Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) = UBound(arrS3(cnt), 2) - 1 ' Put in a value in last array "column"
Else
End If
End Select
'3b) output "row"
Let Ws3.Range("A" & cnt & "").Resize(1, Lc + 1).Value = arrS3(cnt)
Next cnt
Rem 4 ....and after putting the remark clear sheet 1 and sheet 2
Ws1.Cells.ClearContents
Ws2.Cells.ClearContents
End Sub

'If column I is sell
'then see the value of column K &
'if column K is Greater than sheet2 of column E then put the remark in sheet3 in the stock name from column B

'If column I is buy
'see the value of column K &
'if column K is lower than sheet2 of column F then put the remark in sheet3 in the stock name from column B
'remark will be in series like 1,2,3,4,5,6 and so on
'vba is palced in a separate file
'all files are located in same place
'and after putting the remark clear sheet 1 and sheet 2
' http://www.excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort
Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function






I used this code i have not found any error while raning the code sir but this code is not pasting the result sir plz relook sir

[Molly Brennholz]

Hello

Your data ????? :confused:
SELL is K=1100.947 , sheet 2 column E is 1102 … so K is NOT > column E ---- so no output – no remark
BUY is K = 130.734 , sheet 2 column E is 129 … so K is NOT < column E ----- so no output – no remark

See here: http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=12837&viewfull=1#post12837 ....

...I used this code i have not found any error while raning the code sir but this code is not pasting the result ..

Correct. This is as expected. This is what you asked for
no output – no remark = code is not pasting the result = There is no result to paste.

Correct.

Check your data. Check your logic. Explain again what should happen.
Maybe you gave wrong data. Maybe you gave wrong logic.
maybe you asked wromng question.
Or i do not understand what you want.
Try to explain again with more examples

[fixer]

Option Explicit
Sub STEP7() '
Rem 1 Worksheets info
Dim Wbm As Workbook, Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
Set Wbm = Workbooks.Open(ThisWorkbook.Path & "\Merge.xlsx")
Set Ws1 = Wbm.Worksheets("Sheet1"): Set Ws2 = Wbm.Worksheets("Sheet2"): Set Ws3 = Wbm.Worksheets("Sheet3")
Rem 2 data Input
Dim arrS1() As Variant, arrS2() As Variant, arrS3() As Variant
Let arrS1() = Ws1.Range("A1").CurrentRegion.Value: arrS2() = Ws2.Range("A1").CurrentRegion.Value
'2b
ReDim arrS3(1 To UBound(arrS1(), 1)) ' A 1 dimension array of arrays
''2b(i)
' Let arrS3(1) = Ws3.Range("A" & Ws3.Range("A1").CurrentRegion.Columns.Count & "") ' header row as a one dimensional array
''2b(ii) data rows array output

Rem 3
Dim cnt
For cnt = 2 To UBound(arrS1(), 1) ' "row" count, cnt
'2b)(ii)
Dim Lc As Long: Let Lc = Ws3.Cells.Item(cnt, Ws3.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
Let arrS3(cnt) = Ws3.Range("A" & cnt & ":" & CL(Lc + 1) & cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays
Select Case arrS1(cnt, 9) ' column I
Case "SELL" 'If column I is sell
If arrS1(cnt, 11) > arrS2(cnt, 5) Then ' if column K is Greater than sheet2 of column E then

Else
Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) = UBound(arrS3(cnt), 2) - 1 ' Put in a value in last array "column"
End If
Case "BUY" 'If column I is buy
If arrS1(cnt, 11) < arrS2(cnt, 6) Then ' if column K is lower than sheet2 of column F then

Else
Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) = UBound(arrS3(cnt), 2) - 1 ' Put in a value in last array "column"
End If
End Select
'3b) output "row"
Let Ws3.Range("A" & cnt & "").Resize(1, Lc + 1).Value = arrS3(cnt)
Next cnt
Rem 4 ....and after putting the remark clear sheet 1 and sheet 2
Ws1.Cells.ClearContents
Ws2.Cells.ClearContents
Wbm.Save
Wbm.Close

End Sub

'If column I is sell
'then see the value of column K &
'if column K is Greater than sheet2 of column E then put the remark in sheet3 in the stock name from column B

'If column I is buy
'see the value of column K &
'if column K is lower than sheet2 of column F then put the remark in sheet3 in the stock name from column B
'remark will be in series like 1,2,3,4,5,6 and so on
'vba is palced in a separate file
'all files are located in same place
'and after putting the remark clear sheet 1 and sheet 2

Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function





Thnx for the hint sir actually u misunderstood the condition i corrected the same sir
but one issue is there
i ran the code and i send the screenshot of that result plz see the attachment (result.png )
and again i will ran the code and what i need if again the condition met then i need result but it will be in column C and if i again ran the code then it will be in column D
and the number will be in series in column B it will be 1 and if it is column C then 2 and if column D then 3 and so on

[Molly Brennholz]

Sorry, I am having difficulty to understand you.
I do not understand what you are saying.

Upload a file and try again to explain.

[DocAElstein]

Problem is possibly your understanding of English language.

__ < is less than
__ > is greater than



If arrS1(cnt, 11) > arrS2(cnt, 5) Then ----- If column K is Greater than column E Then
If arrS1(cnt, 11) < arrS2(cnt, 5) Then ----- If column K is less than column E Then



3 is greater than 2
2 is less than 3
3 is > 2
2 is < 3

3 > 2 is True
3 > 5 is False
2 < 1 is False
3 > 5 is False




Case "SELL" 'If column I is sell
If arrS1(Cnt, 11) > arrS2(Cnt, 5) Then ' if column K is Greater than sheet2 of column E then
' if column K is Greater than sheet2 of column E then do nothing
Else
' if column K is less than or = to sheet2 of column E then
Let arrS3(Cnt)(1, UBound(arrS3(Cnt), 2)) = UBound(arrS3(Cnt), 2) - 1 ' Put in a value in last array "column"
End If
Case "BUY" 'If column I is buy
If arrS1(Cnt, 11) < arrS2(Cnt, 6) Then ' if column K is lower than sheet2 of column F then
' if column K is lower than sheet2 of column E then do nothing
Else
' if column K is greater than or = to sheet2 of column E then
Let arrS3(Cnt)(1, UBound(arrS3(Cnt), 2)) = UBound(arrS3(Cnt), 2) - 1 ' Put in a value in last array "column"
End If




Molly did understand correct!!

Vixer, You did explain it wrong!!

Molly, you did it correct. Vixer explained it wrong. ( He has difficulty with English language :) )



This is wrong:
If column I is sell then see the value of column K & if column K is Greater than sheet2 of column E then put the remark in sheet3 in the stock name from column B

This is correct:
If column I is sell then see the value of column K & if column K is less than sheet2 of column E then put the remark in sheet3 in the stock name from column B

[fixer]

Plz see the sample file doc sir

[DocAElstein]

Hi

What is the problem??

You want this…Suppose column B already has data
and after that I am runing the macro
then the result will be pasted to column C
and the result which we have to paste is 2
and again when I ran the macro then column C can have the data or it cant have
but if column C has data then the result should be paste as 3 and so on….


Have you tried Molly’s macro ??

I have tried Molly’s macro . ( your version here: http://www.excelfox.com/forum/showthread.php/2433-vba-to-put-remark?p=12846&viewfull=1#post12846 ) it does this:

Start like this
_____ Workbook: Merge (1).xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1Symbol


2ACC


3ADANIENT


4
Worksheet: Sheet3


Now Run it once … It does this

_____ Workbook: Merge (1).xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1Symbol


2ACC
1


3ADANIENT
1


4
Worksheet: Sheet3

Now run it again… It does this

_____ Workbook: Merge (1).xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1Symbol


2ACC
1
2


3ADANIENT
1
2


4
Worksheet: Sheet3

Now run it again… It does this..

_____ Workbook: Merge (1).xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1Symbol


2ACC
1
2
3


3ADANIENT
1
2
3


4
Worksheet: Sheet3

and so on.............................

So it does exactly what you asked for

What is your problem ???


The macro from Molly is doing exactly what you are asking for !!!!




Sub STEP7_() '
Rem 1 Worksheets info
Dim Wbm As Workbook, Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
Set Wbm = Workbooks("Merge (1).xlsx")
' Set Wbm = Workbooks.Open(ThisWorkbook.Path & "\Merge1.xlsx") ' "\Merge.xlsx") ' change to suit
Set Ws1 = Wbm.Worksheets("Sheet1"): Set Ws2 = Wbm.Worksheets("Sheet2"): Set Ws3 = Wbm.Worksheets("Sheet3")
Rem 2 data Input
Dim arrS1() As Variant, arrS2() As Variant, arrS3() As Variant
Let arrS1() = Ws1.Range("A1").CurrentRegion.Value: arrS2() = Ws2.Range("A1").CurrentRegion.Value
'2b
ReDim arrS3(1 To UBound(arrS1(), 1)) ' A 1 dimension array of arrays
''2b(i)
' Let arrS3(1) = Ws3.Range("A" & Ws3.Range("A1").CurrentRegion.Columns.Count & "") ' header row as a one dimensional array
''2b(ii) data rows array output

Rem 3
Dim cnt
For cnt = 2 To UBound(arrS1(), 1) ' "row" count, cnt
'2b)(ii)
Dim Lc As Long: Let Lc = Ws3.Cells.Item(cnt, Ws3.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
Let arrS3(cnt) = Ws3.Range("A" & cnt & ":" & CL(Lc + 1) & cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays
Select Case arrS1(cnt, 9) ' column I
Case "SELL" 'If column I is sell
If arrS1(cnt, 11) > arrS2(cnt, 5) Then ' if column K is Greater than sheet2 of column E then
' do nothing
Else
Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) = UBound(arrS3(cnt), 2) - 1 ' Put in a value in last array "column"
End If
Case "BUY" 'If column I is buy
If arrS1(cnt, 11) < arrS2(cnt, 6) Then ' if column K is lower than sheet2 of column F then
' do nothing
Else
Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) = UBound(arrS3(cnt), 2) - 1 ' Put in a value in last array "column"
End If
End Select
'3b) output "row"
Let Ws3.Range("A" & cnt & "").Resize(1, Lc + 1).Value = arrS3(cnt)
Next cnt
Rem 4 ....and after putting the remark clear sheet 1 and sheet 2
' Ws1.Cells.ClearContents
' Ws2.Cells.ClearContents
' Wbm.Save
' Wbm.Close

End Sub


Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

[Molly Brennholz]

Thanks, Alam, for the confirmation
Molly
x

[fixer]

Sorry Doc Sir the code is perfect no doubt and thnx molly Sir and Doc Sir for the Great Help

[fixer]

one more condition i have to add doc sir
if condition met then it will paste the result but if condition doesnt met then see the sample pic
let us assume again i am runing the code and acc mets the condition then C2 gets the result as 2
& adanient doesnt met the condition then it should clear the data from cloumn B till the end of the data in that entire row
means the adanient will be there in that row & in that row there will not be any data sir so plz doc sir have a relook sir

[DocAElstein]

Hello

You may possibly have uploaded the wrong sample pic?
The sample pic does not explain anything…




I think you are having difficulty yourself understanding what you want. I do not think you understand anything about what is going on.
Or
You are almost completely unable to explain anything correctly in English.



This is what Molly’s macro does :-

The conditions which are met are in current macro ( http://www.excelfox.com/forum/showthread.php/2433-vba-to-put-remark?p=12850&viewfull=1#post12850 ) are these… …

For each row – the two condition is met scenarios….

If column I is SELL then see the value of column K & if column K is less than or equal to column E in sheet 2 then put the remark in sheet3

If column I is BUY then see the value of column K & if column K is greater than or equal t column F in sheet 2 then put the remark in sheet3

The remark will be in series like 1,2,3,4,5,6 …… and so on. So the remark in column B will always be 1. The remark in column C will always be 2 … and so on


Currently, if the condition is not met then ‘ Nothing is done


Case "SELL" 'If column I is sell
If arrS1(cnt, 11) > arrS2(cnt, 5) Then ' if column K is Greater than sheet2 of column E then
' do nothing - Nothing is done - column K is Greater than column E of sheet2
Else



Case "BUY" 'If column I is buy
If arrS1(cnt, 11) < arrS2(cnt, 6) Then ' if column K is lower than sheet2 of column F then
' do nothing - Nothing is done - column K is lower than column F of sheet2
Else




We can only make guesses at what you might want…
‘ Condition not met ... clear the data from column B till the end of the data in that entire row



Select Case arrS1(cnt, 9) ' column I

Case "SELL" 'If column I is SELL
If arrS1(cnt, 11) > arrS2(cnt, 5) Then ' if column K is Greater than column E of sheet2 Then
' Condition not met ... clear the data from column B till the end of the data in that entire row
Ws3.Range("B" & cnt & ":B" & CL(Lc) & "").ClearContents
Else
' Condition is met
Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) = UBound(arrS3(cnt), 2) - 1 ' Put in a value in last array "column"
End If

Case "BUY" 'If column I is BUY
If arrS1(cnt, 11) < arrS2(cnt, 6) Then ' if column K is lower than column F of sheet2 Then
' Condition is not met ....clear the data from column B till the end of the data in that entire row
Ws3.Range("B" & cnt & ":B" & CL(Lc) & "").ClearContents
Else
' Condition is met
Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) = UBound(arrS3(cnt), 2) - 1 ' Put in a value in last array "column"
End If
End Select
' Edit: see corrrection post #16 below : http://www.excelfox.com/forum/showthread.php/2433-vba-to-put-remark?p=12858&viewfull=1#post12858





The biggest problem remains that you cannot explain anything correctly in English. The biggest problem is not Excel or Excel VBA. The biggest problem is the communication difficulty between us

Have you tried this ?
https://translate.google.de/?hl=de#view=home&op=translate&sl=mr&tl=en
https://translate.google.de/?hl=de#view=home&op=translate&sl=en&tl=mr&text=Hello.%20%0AWhat%20is%20your%20native%20langu age%3F



Alan

[DocAElstein]

Please try to understand this:

Copy all three macros to the same code module

Only Run macro Sub CallIfElse()


Sub CallIfElse()
Call IfElse1(2)
Call IfElse1(3)
Call IfElse1(4)

Call IfElse2(2)
Call IfElse2(3)
Call IfElse2(4)
End Sub
Sub IfElse1(ByVal A_Number As Long)
If A_Number > 3 Then ' If a number is greater Than 3 then
' condition (A_Number > 3 ) is met
' condition (A_Number < 3 or A_Number = 3 ) is not met
MsgBox prompt:="Your number is " & A_Number & " It is greater than 3"
Else
' condition (A_Number > 3 ) is not met
' condition (A_Number < 3 or A_Number = 3 ) is met
MsgBox prompt:="Your number is " & A_Number & " It is less than or it is equal to 3"
End If
End Sub
Sub IfElse2(ByVal A_Number As Long)
If A_Number < 3 Then ' If a number is less Than 3 then
' condition (A_Number < 3) is met
' condition (A_Number > 3 or A_Number = 3) is not met
MsgBox prompt:="Your number is " & A_Number & " It is less than 3"
Else
' condition (A_Number < 3) is not met
' condition (A_Number > 3 or A_Number = 3) is met
MsgBox prompt:="Your number is " & A_Number & " It is greater than or it is equal to 3"
End If
End Sub

[fixer]

Doc Sir the code is doing perfect things
only this line creating error
Ws3.Range("B" & cnt & ":B" & CL(Lc) & "").ClearContents

rest everyhing is 101% perfect Doc sir

[DocAElstein]

my mistake....it should be ...
Ws3.Range("B" & cnt & ":" & CL(Lc) & cnt & "").ClearContents

Cnt is row number , like 2 or 3
Lc is last column number , like 2 or 3 or 4 or 5 .... and so on
CL(Lc) is last column Letter like B or C or D or E ..... and so on


So
__ "B" & cnt & ":" & CL(Lc) & cnt & "" __
is like
__ “B2:D2”
or
__ “B2:F2”
or
__ “B3:G3”
.. and so on

[fixer]

Ws3.Range("B" & cnt & ":" & CL(Lc) & cnt & "").ClearContents
I putted this code i am not getting any error but it is not clearing the data
i tried this also
Ws3.Range("B" & cnt & ":" & CL(Lc) & cnt & "").cells.ClearContents
but this is also not clearing the data Doc Sir plz relook sir

[DocAElstein]

Post the macro that you are using

[fixer]

Option Explicit
Sub STEP7() '
Rem 1 Worksheets info
Dim Wbm As Workbook, Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
Set Wbm = Workbooks.Open(ThisWorkbook.Path & "\Merge.xlsx")
Set Ws1 = Wbm.Worksheets("Sheet1"): Set Ws2 = Wbm.Worksheets("Sheet2"): Set Ws3 = Wbm.Worksheets("Sheet3")
Rem 2 data Input
Dim arrS1() As Variant, arrS2() As Variant, arrS3() As Variant
Let arrS1() = Ws1.Range("A1").CurrentRegion.Value: arrS2() = Ws2.Range("A1").CurrentRegion.Value
'2b
ReDim arrS3(1 To UBound(arrS1(), 1)) ' A 1 dimension array of arrays
''2b(i)
' Let arrS3(1) = Ws3.Range("A" & Ws3.Range("A1").CurrentRegion.Columns.Count & "") ' header row as a one dimensional array
''2b(ii) data rows array output

Rem 3
Dim cnt
For cnt = 2 To UBound(arrS1(), 1) ' "row" count, cnt
'2b)(ii)
Dim Lc As Long: Let Lc = Ws3.Cells.Item(cnt, Ws3.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
Let arrS3(cnt) = Ws3.Range("A" & cnt & ":" & CL(Lc + 1) & cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays
Select Case arrS1(cnt, 9) ' column I
Case "SELL" 'If column I is sell
If arrS1(cnt, 11) > arrS2(cnt, 5) Then ' if column K is Greater than sheet2 of column E then
Ws3.Range("B" & cnt & ":" & CL(Lc) & cnt & "").Cells.ClearContents
Else
Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) = UBound(arrS3(cnt), 2) - 1 ' Put in a value in last array "column"
End If
Case "BUY" 'If column I is buy
If arrS1(cnt, 11) < arrS2(cnt, 6) Then ' if column K is lower than sheet2 of column F then
Ws3.Range("B" & cnt & ":" & CL(Lc) & cnt & "").Cells.ClearContents
Else
Let arrS3(cnt)(1, UBound(arrS3(cnt), 2)) = UBound(arrS3(cnt), 2) - 1 ' Put in a value in last array "column"
End If
End Select
'3b) output "row"
Let Ws3.Range("A" & cnt & "").Resize(1, Lc + 1).Value = arrS3(cnt)
Next cnt
Rem 4 ....and after putting the remark clear sheet 1 and sheet 2
Ws1.Cells.ClearContents
Ws2.Cells.ClearContents
Wbm.Save
Wbm.Close

End Sub

'If column I is sell
'then see the value of column K &
'if column K is Greater than sheet2 of column E then put the remark in sheet3 in the stock name from column B

'If column I is buy
'see the value of column K &
'if column K is lower than sheet2 of column F then put the remark in sheet3 in the stock name from column B
'remark will be in series like 1,2,3,4,5,6 and so on
'vba is palced in a separate file
'all files are located in same place
'and after putting the remark clear sheet 1 and sheet 2

Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7EqbG23kg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7EqbG23kg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7KvJXmK 8o (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7KvJXmK 8o)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7E1gwg4Aq (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7E1gwg4Aq)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79hNGvJ bu (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79hNGvJ bu)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79YAfa2 4T (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79YAfa2 4T)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79M1SYH 1E (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79M1SYH 1E)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78SxhXT nR (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78SxhXT nR)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78GftO_ iE (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78GftO_ iE)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h77HSGDH 4A (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h77HSGDH 4A)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h76fafzc EJ (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h76fafzc EJ)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h759YIjl aG (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h759YIjl aG)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h74pjGcb Eq (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h74pjGcb Eq)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg.9h5uPRbWIZl9h7165DZd jg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg.9h5uPRbWIZl9h7165DZd jg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

The data is being cleared, But it is being put back in!


To explain:-
These two code lines clear the range when the condition is not met
Ws3.Range("B" & cnt & ":" & CL(Lc) & cnt & "").ClearContents
Ws3.Range("B" & cnt & ":" & CL(Lc) & cnt & "").ClearContents

This next code line puts all the data in. It does this after the Select Case / End Select
Let Ws3.Range("A" & cnt & "").Resize(1, Lc + 1).Value = arrS3(cnt)
Because it does this after the Select Case / End Select, the code line will be done both when the condition is met and when the condition is not met.

So , the range is being cleared if the condition is not met. But it is then being re filled.
The ranges are filled from the array, arrS3(Cnt)


There are two possibilities to overcome this problem.

_1 empty the array , ( instead of clearing the range )


Dim Cnt As Long, Clms As Long
For Cnt = 2 To UBound(arrS1(), 1) ' "row" count, cnt
'2b)(ii)
Dim Lc As Long: Let Lc = Ws3.Cells.Item(Cnt, Ws3.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
Let arrS3(Cnt) = Ws3.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays
Select Case arrS1(Cnt, 9) ' column I
Case "SELL" 'If column I is SELL
If arrS1(Cnt, 11) > arrS2(Cnt, 5) Then ' if column K is Greater than column E of sheet2 Then
' Condition not met ... clear the data from cloumn B till the end of the data in that entire row
' Ws3.Range("B" & Cnt & ":" & CL(Lc) & Cnt & "").ClearContents
For Clms = 2 To Lc
Let arrS3(Cnt)(1, Clms) = ""
Next Clms
Else
' Condition is met
Let arrS3(Cnt)(1, UBound(arrS3(Cnt), 2)) = UBound(arrS3(Cnt), 2) - 1 ' Put in a value in last array "column"
End If

Case "BUY" 'If column I is BUY
If arrS1(Cnt, 11) < arrS2(Cnt, 6) Then ' if column K is lower than column F of sheet2 Then
' Condition is not met ....clear the data from cloumn B till the end of the data in that entire row
' Ws3.Range("B" & Cnt & ":" & CL(Lc) & Cnt & "").ClearContents
For Clms = 2 To Lc
Let arrS3(Cnt)(1, Clms) = ""
Next Clms
Else
' Condition is met
Let arrS3(Cnt)(1, UBound(arrS3(Cnt), 2)) = UBound(arrS3(Cnt), 2) - 1 ' Put in a value in last array "column"

End If
End Select
' '3b) output "row"
Let Ws3.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arrS3(Cnt)
Next Cnt



OR:-

_ 2 Use the code line which puts in all the data for the met condition within the Select Case / End Select


Rem 3
Dim Cnt As Long
For Cnt = 2 To UBound(arrS1(), 1) ' "row" count, cnt
'2b)(ii)
Dim Lc As Long: Let Lc = Ws3.Cells.Item(Cnt, Ws3.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
Let arrS3(Cnt) = Ws3.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays
Select Case arrS1(Cnt, 9) ' column I
Case "SELL" 'If column I is SELL
If arrS1(Cnt, 11) > arrS2(Cnt, 5) Then ' if column K is Greater than column E of sheet2 Then
' Condition not met ... clear the data from cloumn B till the end of the data in that entire row
Ws3.Range("B" & Cnt & ":" & CL(Lc) & Cnt & "").ClearContents
Else
' Condition is met
Let arrS3(Cnt)(1, UBound(arrS3(Cnt), 2)) = UBound(arrS3(Cnt), 2) - 1 ' Put in a value in last array "column"
Let Ws3.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arrS3(Cnt)
End If

Case "BUY" 'If column I is BUY
If arrS1(Cnt, 11) < arrS2(Cnt, 6) Then ' if column K is lower than column F of sheet2 Then
' Condition is not met ....clear the data from cloumn B till the end of the data in that entire row
Ws3.Range("B" & Cnt & ":" & CL(Lc) & Cnt & "").ClearContents
Else
' Condition is met
Let arrS3(Cnt)(1, UBound(arrS3(Cnt), 2)) = UBound(arrS3(Cnt), 2) - 1 ' Put in a value in last array "column"
Let Ws3.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arrS3(Cnt)
End If
End Select
'' '3b) output "row"
' Let Ws3.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arrS3(Cnt)
Next Cnt

[fixer]

Thnx Alot Doc Sir and Molly Sir
Thnx for ur Great Support sir

[DocAElstein]

You are welcome.

( Molly is a Lady ( Woman ) - She is not a Sir, She is a Ma'am or Madam )

Thank you Molly, Ma'am

Sir - Man
Ma'am - Woman

[fixer]

Thnx Doc Sir & Molly Mam For the Great Help

[Molly Brennholz]

Glad you got the answer eventually

[fixer]

If column B of 2.xlsm match with column B of 1.xls then paste the data from column C of 2.xls as 1,2,3,4,5 and so on....
&
If column B of 2.xlsm doesn't match with column B of 1.xls then delete all the data from column C of that row

macro will be placed in 2.xlsm
all files re located in different path
sheet name can be anything
plz see the sample file

[Molly Brennholz]

Hello Vixer.
This is similar to what I did for you before, I think.

Here I have some new explanations for you Here
http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=13165&viewfull=1#post13165
http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=13164&viewfull=1#post13164





Before:

_____ Workbook: 1.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G

1NSE
25
6>
50000A


2NSE
22
6>
10000A


3NSE
15083
6>
70000A


4NSE
17388
6>
20000A


5NSE
100
6>
170000A


6
Worksheet: Sheet1 (4)

_____ Workbook: 2.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H

1Symbol


2ACC
22
1
2


3ADANIENT
25
1


4ADANIPORTS
15083
1
2
3


5ADANIPOWER
17388
1
2
3
4
5


6AMARAJABAT
100
1
2
3
4


7ASIANPAINT
236
1
2


8
Worksheet: Sheet1


now, run macro from here:
http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=13166&viewfull=1#post13166

After results

_____ Workbook: 2.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H

1Symbol


2ACC
22
1
2
3


3ADANIENT
25
1
2


4ADANIPORTS
15083
1
2
3
4


5ADANIPOWER
17388
1
2
3
4
5
6


6AMARAJABAT
100
1
2
3
4
5


7ASIANPAINT
236


8
Worksheet: Sheet1



Molly

[fixer]

Yes Molly Mam Actually it requires the modification to increase the output
Sorry for the same Mam But in future there will not be any question similar to this

[Molly Brennholz]

No need to apologise, I was just making a passing remark, that’s all
It was easy for me to do , because of the simularities to the previous Thread, that's all
:) .

[fixer]

Thnx Alot Molly Mam for helping me in solving this problem
Have a Great Day
U have mentioned that ignore that post so till today i have not checked that code but today i thought to check the code if there will be any error i will try to solve it
But the code was perfect

' http://www.excelfox.com/forum/showthread.php/2465-copy-paste-conditional


Sub CopyPasterConditionalToPutRemark_1_2_3_etc() '
Rem 1 Worksheets info
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
Set Wb1 = Workbooks("1.xlsx")
Set Wb2 = ThisWorkbook ' macro will be placed in 2.xlsm
Set Ws1 = Wb1.Worksheets.Item(1): Set Ws2 = Wb2.Worksheets.Item(1)
Rem 2 data Input
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value
Let arr2() = Ws2.Range("A1").CurrentRegion.Value ' Current region will not work for arrS1() because columns G to J are empty
'2b
ReDim arr3(0 To UBound(arr2(), 1)) ' A 1 dimension array of arrays , ( the first element arr3(0) we will not use )
''2b(i)
' Let arrS3(1) = Ws3.Range("A" & Ws3.Range("A1").CurrentRegion.Columns.Count & "") ' header row as a one dimensional array
''2b(ii) data rows array output
Rem 3
Dim Cnt
For Cnt = 2 To UBound(arr2(), 1) ' "row" count, Cnt from after heading untill last row in 2.xlsm ( Ws2 )
'2b)(ii) make and fill the row element array inside the current arr3(cnt) element
Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column ' last column in this row cnt
Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value ' - returns an array of 1 "row" into this element of the array of arrays. It has one more element than filled columns - this empty last element is filled in the next line
Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2 ' this puts the next integer in the last, currently empty element
'3a) Check for match criteria
Dim mtchRes As Variant
Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0)
If IsError(mtchRes) Then ' If the last line errored than we did not find a match, so from the 3rd up to the last element need to be rtemoved from the array for this row
Dim Empt As Long
For Empt = 3 To UBound(arr3(Cnt - 1), 2)
Let arr3(Cnt - 1)(1, Empt) = ""
Next Empt
Else
' a match was found, so we do not need to remove the 1 2 3 etc...
End If
'3c) Paste out row
Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1)
Next Cnt
Rem 4 ....and after putting the remark clear sheet 1 and sheet 2
' Ws1.Cells.Clear
' Ws2.Cells.Clear
End Sub



















' http://www.excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort
Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function
I used this vba code and it is giving perfect output if any error u know plz let me know
for me it is working perfect as per my needs

[fixer]

I am really Sorry Molly Mam its was my fault
little modification in the code is required
plz see the sample file

[Molly Brennholz]

Hello.

...U have mentioned that ignore that post so till today i have not checked that code but today i thought to check the code if there will be any error i will try to solve it ..... :confused:
I am not really sure what you are saying here.
I am sorry, but I do not understand you very well.
I think you have difficulty speaking in English.
( Just out of interest, where do you come from?. What is your mother tongue? Have you tried this https://translate.google.de/?hl=de#view=home&op=translate&sl=hi&tl=en )





.. little modification in the code is required
plz see the sample file
This was last macro …
If column B of 2.xlsm match with column B of 1.xls then paste the data from column C of 2.xls as 1,2,3,4,5 and so on....
&
If column B of 2.xlsm doesn't match with column B of 1.xls then delete all the data from column C of that row

If IsError(mtchRes) Then ' If the last line errored than we did not find a match, so from the 3rd up to the last element need to be removed from the array for this row
Dim Empt As Long
For Empt = 3 To UBound(arr3(Cnt - 1), 2)
Let arr3(Cnt - 1)(1, Empt) = ""
Next Empt
Else
' a match was found, so we do not need to remove the 1 2 3 etc...
End If

_.______________________

This is new requirement…
If column B of 2.xlsm match with column B of 1.xls then clear all the data in that row from column C
If column B of 2.xlsm does not match with column B of 1.xls then paste the data from column C of 2.xls as 1,2,3,4,5 and so on…
New requirement is almost the same, but it is just now
The other way around,
or
Upside down
or,
Standing on its head,
or
Arse over Tit

If IsError(mtchRes) Then ' If the last line errored than we did not find a match, so we do not need to do anything to the array
' a match was not found, so we do not need to remove the 1 2 3 etc...
Else
' a match was found, so we need to remove the 1 2 3 etc...
Dim Empt As Long
For Empt = 3 To UBound(arr3(Cnt - 1), 2)
Let arr3(Cnt - 1)(1, Empt) = ""
Next Empt
End If
( Full macro is here:
Sub CopyPasterConditionalToPutRemark_1_2_3_etcArseOver Tit() http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=13176&viewfull=1#post13176 )

Before:
_____ Workbook: 1 1mai.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G

1NSE
15083
6>
70000A


2NSE
17388
6>
20000A


3NSE
100
6>
170000A


4
Worksheet: Sheet1

_____ Workbook: 2 1Mai.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L

1Symbol


2ACC
22
1


3ADANIENT
25
1
2
3


4ADANIPORTS
15083
1
2
3
4
5


5ADANIPOWER
17388
1


6AMARAJABAT
100
1
2
34
5
6
7
8
9
10


7ASIANPAINT
236
1
2


8
Worksheet: Sheet1



After running macro here: http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=13176&viewfull=1#post13176

_____ Workbook: 2 1Mai.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L

1Symbol


2ACC
22
1
2


3ADANIENT
25
1
2
3
4


4ADANIPORTS
15083


5ADANIPOWER
17388


6AMARAJABAT
100


7ASIANPAINT
236
1
2
3


8
Worksheet: Sheet1



Molly

[fixer]

Thnx Alot Mam for giving ur Precious Time and Great Support in solving this Problem
Problem Solved
Have a Great Day

[fixer]

Just out of interest, where do you come from?. What is your mother tongue?

Myself Avinash Reside in Mumbai from India & mother tongue Hindi

[Molly Brennholz]

Thanks for that info.
I am sorry to see that you have been banned.
But I can understand why. You really must at least try a little to read and understand, or at the very least read what we write once.
In your haste, you did not even see initially that I had written a full working macro for you here. Only by chance you noticed later!! I am sure you have ignored and / or missed much that has been done for you.
This is very impolite and disrespectful. You should at least have the decency to read all we write. Then after you can ask for more explanations, or more help.
But just continually asking and not even reading what we write is crazy

I hope all works out well for you. Alan is a lovely guy. For him to ban anyone is something I never thought I would ever live to see…

Take care and stay healthy
Molly
x

[fixer]

Molly Mam i completely agree with u & Doc Sir & i know its my mistake & Sorry for the same Mam

[fixer]

All files are located in different path
sheet name can be anything
I have a file ap.xls & Book1.xlsm
macro will be placed in Book1.xlsm
We have to look on Column S of ap.xls and If column S of ap.xls has negative numbers then we have to look on Column E of ap.xls & we will match that Column E data of ap.xls with column A of sheet1 of Book1.xlsm & if it matches then we will see wheather column C of Book1.xlsm has data in it or not & if column C of Book1.xlsm has data in it then do nothing & if column C of Book1.xlsm doesn't have data then we will go to sheet3 of Book1.xlsm and we will look for a match of Column E data of ap.xls with column A of sheet3 of Book1.xlsm & if it is found then we will copy the data from sheet3 of Book1.xlsm and paste it to sheet1 of book1.xlsm & we increase one more number in series in it

Sheet4 is the output that i need in Sheet1 (Result is pasted in sheet4 only for understanding purpose, i need result in sheet1 only)



plz see the sample file

[Molly Brennholz]

Hello Avinash.
Good to see you back!

I have written a macro for you here: https://excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=13417&viewfull=1#post13417



I have written 'comments to explain the macro
We all understand that you do not want to learn any VBA. But it is madness to not attempt to understand anything of the coding which we give you.
I assume you will want to use these codings later for something important to you?
If this is the case, then you will fail, because we will not be with you later every step of the adventure.
You must make at least some attempt to understand the coding.

But its your choice.

Molly



Macro is also in Book1.xlsm
Share 'Book1.xlsm' : https://app.box.com/s/qotw65wmiq1aln7frg9o5gys8ke1l8xh
Share 'ap.xls' : https://app.box.com/s/r0tc0hkwoxrqjsqfu4gh7eqyy5jmqlg2

[fixer]

Thnx Alot Molly Mam For giving ur Great Support & helping me in solving this problem Mam

We all understand that you do not want to learn any VBA. But it is madness to not attempt to understand anything of the coding which we give you. (Thnx for understanding Me, But I Was Busy in making of Something & Now i am very near to get free time to do some work & then i will play with vba )


we will not be with you later every step of the adventure.(That Day will be the worst day of mine But i will some how manage it & i don't want that day should come)



Problem Solved

[fixer]

Moderstor notice:
Copy of a similar post in another forum, just for future referrence... ( Post made by Avinash in one of his many forum names ,
https://www.excelforum.com/excel-programming-vba-macros/1319768-if-condition-met-then-put-the-remark-between-files.html )


Hi there, I am looking for a macro As mentioned below
Plz see the sample file attached below
If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is not equal to column E of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is equal to column E of 1.xls then don't do anything
Plz note: Macro will be placed in macro.xlsm which i have attached below & 1.xls & macro.xlsm may be in different path so I will edit the path As per my needs
_____ Workbook: 1 20June excelforum.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1200
1200
1173
1194.2
1194.2
22


3NSEADANIENTEQ
141.4
143.7
141.4
141.05
141.05
25


4NSEADANIPORTSEQ
309
309
309
309.25
309.25
15083


5NSEADANIPOWEREQ
33.3
33.3
32.1
32.35
32.35
17388


6NSEAMARAJABATEQ
555
578
555
572.85
572.85
100


7NSEASIANPAINTEQ
1529
1529
1529
1552.95
1552.95
236


8
Worksheet: 1-Sheet1 20 June excelforum

_____ Workbook: Macro 20June excelforum.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M

1Symbol


2ACC
22
1
2
3
4


3ADANIENT
25
1
2
3


4ADANIPORTS
15083
1


5ADANIPOWER
17388
1
2
3
4
5
6
7


6AMARAJABAT
100
1
2
3
4
5
6
7
8
9
10
11


7ASIANPAINT
236


8AMBUJACEM
1270


9APOLLOHOSP
157


10APOLLOPIPE
14361


11ASHOKLEY
212


12AUROPHARMA
275


13AXISBANK
5900


14BAJAJ-AUTO
16669
Worksheet: Sheet1 20 June excelforum



Original question in this Thread: Post#1
https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc
https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc
If column I is sell then see the value of column K & if column K is Greater than sheet2 of column E then put the remark in sheet3 in the stock name from column B
If column I is buy then see the value of column K & if column K is lower than sheet2 of column F then put the remark in sheet3 in the stock name from column B
remark will be in series like 1,2,3,4,5,6 and so on
vba is palced in a separate file
all files are located in same place
and after putting the remark clear sheet 1 and sheet 2

Post #25:
https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=13161&viewfull=1#post13161
https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=13161&viewfull=1#post13161
If column B of 2.xlsm match with column B of 1.xls then paste the data from column C of 2.xls as 1,2,3,4,5 and so on....
&
If column B of 2.xlsm doesn't match with column B of 1.xls then delete all the data from column C of that row

macro will be placed in 2.xlsm
all files re located in different path
sheet name can be anything

[DocAElstein]

_____ Workbook: 1 20June excelforum.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1200
1200
1173
1194.2
1194.2
22


3NSEADANIENTEQ
141.4
143.7
141.4
141.05
141.05
25


4NSEADANIPORTSEQ
309
309
309
309.25
309.25
15083


5NSEADANIPOWEREQ
33.3
33.3
32.1
32.35
32.35
17388


6NSEAMARAJABATEQ
555
578
555
572.85
572.85
100


7NSEASIANPAINTEQ
1529
1529
1529
1552.95
1552.95
236


8
Worksheet: 1-Sheet1 20 June excelforum
If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is not equal to column E of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is equal to column E of 1.xls then don't do anything
Plz note: Macro will be placed in macro.xlsm which i have attached below & 1.xls & macro.xlsm may be in different path so I will edit the path As per my needs
_____ Workbook: Macro 20June excelforum.xlsm ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJKLM
1Symbol

2ACC221234

3ADANIENT25123

4ADANIPORTS150831

5ADANIPOWER173881234567

6AMARAJABAT1001234567891011

7ASIANPAINT236
Worksheet: Sheet1 20 June excelforum


Symbol
ACC 22 1 2 3 4
ADANIENT 25 1 2 3
ADANIPORTS 15083 1
ADANIPOWER 17388 1 2 3 4 5 6 7
AMARAJABAT 100 1 2 3 4 5 6 7 8 9 10 11
ASIANPAINT 236
AMBUJACEM 1270
APOLLOHOSP 157
APOLLOPIPE 14361
ASHOKLEY 212
AUROPHARMA 275
AXISBANK 5900
BAJAJ-AUTO 16669
BAJAJFINSV 16675
BAJFINANCE 317
BALKRISIND 335
BANKBARODA 4668
BATAINDIA 371
BEL 383
BERGEPAINT 404
BHARATFORG 422
BHARTIARTL 10604
BHEL 438
BIOCON 11373
BOSCHLTD 2181
BPCL 526
BRITANNIA 547
CADILAHC 7929
CANBK 10794
CASTROLIND 1250
CENTURYTEX 625
CESC 628
CHOLAFIN 685
CIPLA 694
COALINDIA 20374
COLPAL 15141
CONCOR 4749
CUMMINSIND 1901
DABUR 772
DIVISLAB 10940
DLF 14732
DRREDDY 881
EICHERMOT 910
EQUITAS 16852
ESCORTS 958
EXIDEIND 676
FEDERALBNK 1023
GAIL 4717
GLENMARK 7406
GMRINFRA 13528
GODREJCP 10099
GRASIM 1232
HAVELLS 9819
HCLTECH 7229
HDFC 1330
HDFCBANK 1333
HEROMOTOCO 1348
HINDALCO 1363
HINDPETRO 1406
HINDUNILVR 1394
IBULHSGFIN 30125
ICICIBANK 4963
ICICIPRULI 18652
IDEA 14366
IDFCFIRSTB 11184
IGL 11262
INDIGO 11195
INDUSINDBK 5258
INFRATEL 29135
INFY 1594
IOC 1624
ITC 1660
JINDALSTEL 6733
JSWSTEEL 11723
JUBLFOOD 18096
JUSTDIAL 29962
KOTAKBANK 1922
L&TFH 24948
LICHSGFIN 1997
LT 11483
LUPIN 10440
M&M 2031
M&MFIN 13285
MANAPPURAM 19061
MARICO 4067
MARUTI 10999
MCDOWELL-N 10447
MFSL 2142
MGL 17534
MINDTREE 14356
MOTHERSUMI 4204
MRF 2277
MUTHOOTFIN 23650
NATIONALUM 6364
NBCC 31415
NCC 2319
NESTLEIND 17963
NIITTECH 11543
NMDC 15332
NTPC 11630
OIL 17438
ONGC 2475
PAGEIND 14413
PEL 2412
PETRONET 11351
PFC 14299
PIDILITIND 2664
PNB 10666
POWERGRID 14977
PVR 13147
RAMCOCEM 2043
RBLBANK 18391
RECLTD 15355
RELIANCE 2885
SAIL 2963
SBIN 3045
SHREECEM 3103
SIEMENS 3150
SRF 3273
SRTRANSFIN 4306
SUNPHARMA 3351
SUNTV 13404
TATACHEM 3405
TATAMOTORS 3456
TATAMTRDVR 16965
TATAPOWER 3426
TATASTEEL 3499
TCS 11536
TECHM 13538
TITAN 3506
TORNTPHARM 3518
TORNTPOWER 13786
TVSMOTOR 8479
UBL 16713
UJJIVAN 17069
ULTRACEMCO 11532
UPL 11287
VEDL 3063
VOLTAS 3718
WIPRO 3787
ZEEL 3812

[DocAElstein]

This and the next few posts are purely done out of interest looking at the alternative solutions supplied elsewhere for the similar question…. vba Copy Paste Conditional to put remark 1 2 3 .. etc…..

Here we analyse the solutions to this….



_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1200
1200
1173
1194.2
1194.2
22


3NSEADANIENTEQ
141.4
143.7
141.4
141.05
141.05
25


4NSEADANIPORTSEQ
309
309
309
309.25
309.25
15083


5NSEADANIPOWEREQ
33.3
33.3
32.1
32.35
32.35
17388


6NSEAMARAJABATEQ
555
578
555
572.85
572.85
100


7NSEASIANPAINTEQ
1529
1529
1529
1552.95
1552.95
236


8
Worksheet: 1-Sheet1 20 June excelforum

If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is not equal to column E of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is equal to column E of 1.xls then don't do anything
Plz note: Macro will be placed in macro.xlsm which i have attached below & 1.xls & macro.xlsm may be in different path so I will edit the path As per my needs
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJKLM
1Symbol

2ACC221234

3ADANIENT25123

4ADANIPORTS150831

5ADANIPOWER173881234567

6AMARAJABAT1001234567891011

7ASIANPAINT236
Worksheet: Sheet1 20 June excelforum


Symbol
ACC 22 1 2 3 4
ADANIENT 25 1 2 3
ADANIPORTS 15083 1
ADANIPOWER 17388 1 2 3 4 5 6 7
AMARAJABAT 100 1 2 3 4 5 6 7 8 9 10 11
ASIANPAINT 236
AMBUJACEM 1270
APOLLOHOSP 157
APOLLOPIPE 14361
ASHOKLEY 212
AUROPHARMA 275
AXISBANK 5900
BAJAJ-AUTO 16669
BAJAJFINSV 16675
BAJFINANCE 317
BALKRISIND 335
BANKBARODA 4668
BATAINDIA 371
BEL 383
BERGEPAINT 404
BHARATFORG 422
BHARTIARTL 10604
BHEL 438
BIOCON 11373
BOSCHLTD 2181
BPCL 526
BRITANNIA 547
CADILAHC 7929
CANBK 10794
CASTROLIND 1250
CENTURYTEX 625
CESC 628
CHOLAFIN 685
CIPLA 694
COALINDIA 20374
COLPAL 15141
CONCOR 4749
CUMMINSIND 1901
DABUR 772
DIVISLAB 10940
DLF 14732
DRREDDY 881
EICHERMOT 910
EQUITAS 16852
ESCORTS 958
EXIDEIND 676
FEDERALBNK 1023
GAIL 4717
GLENMARK 7406
GMRINFRA 13528
GODREJCP 10099
GRASIM 1232
HAVELLS 9819
HCLTECH 7229
HDFC 1330
HDFCBANK 1333
HEROMOTOCO 1348
HINDALCO 1363
HINDPETRO 1406
HINDUNILVR 1394
IBULHSGFIN 30125
ICICIBANK 4963
ICICIPRULI 18652
IDEA 14366
IDFCFIRSTB 11184
IGL 11262
INDIGO 11195
INDUSINDBK 5258
INFRATEL 29135
INFY 1594
IOC 1624
ITC 1660
JINDALSTEL 6733
JSWSTEEL 11723
JUBLFOOD 18096
JUSTDIAL 29962
KOTAKBANK 1922
L&TFH 24948
LICHSGFIN 1997
LT 11483
LUPIN 10440
M&M 2031
M&MFIN 13285
MANAPPURAM 19061
MARICO 4067
MARUTI 10999
MCDOWELL-N 10447
MFSL 2142
MGL 17534
MINDTREE 14356
MOTHERSUMI 4204
MRF 2277
MUTHOOTFIN 23650
NATIONALUM 6364
NBCC 31415
NCC 2319
NESTLEIND 17963
NIITTECH 11543
NMDC 15332
NTPC 11630
OIL 17438
ONGC 2475
PAGEIND 14413
PEL 2412
PETRONET 11351
PFC 14299
PIDILITIND 2664
PNB 10666
POWERGRID 14977
PVR 13147
RAMCOCEM 2043
RBLBANK 18391
RECLTD 15355
RELIANCE 2885
SAIL 2963
SBIN 3045
SHREECEM 3103
SIEMENS 3150
SRF 3273
SRTRANSFIN 4306
SUNPHARMA 3351
SUNTV 13404
TATACHEM 3405
TATAMOTORS 3456
TATAMTRDVR 16965
TATAPOWER 3426
TATASTEEL 3499
TCS 11536
TECHM 13538
TITAN 3506
TORNTPHARM 3518
TORNTPOWER 13786
TVSMOTOR 8479
UBL 16713
UJJIVAN 17069
ULTRACEMCO 11532
UPL 11287
VEDL 3063
VOLTAS 3718
WIPRO 3787
ZEEL 3812

[DocAElstein]

The solution from karmapala https://www.excelforum.com/excel-programming-vba-macros/1319768-if-condition-met-then-put-the-remark-between-files.html#post5353174

Sub karmapala() ' https://www.excelforum.com/excel-programming-vba-macros/1319768-if-condition-met-then-put-the-remark-between-files.html#post5353174
Dim arr() As Variant

Set wb1 = Workbooks("1.xls")
Set sh1 = wb1.Sheets("1-Sheet1")
Set Rng = sh1.Range("D2", sh1.Range("D" & Rows.Count).End(xlUp))
Set wb2 = Workbooks("Macro.xlsm")
Set sh2 = wb2.Sheets("Sheet1")
x = 0

For Each cell In Rng
If cell.Value = cell.Offset(0, 1).Value And cell.Value <> cell.Offset(0, 2).Value Then
ReDim Preserve arr(x)
arr(x) = cell.Offset(0, 5)
x = x + 1
End If

If cell.Value <> cell.Offset(0, 1) And cell.Value = cell.Offset(0, 2) Then
ReDim Preserve arr(x)
arr(x) = cell.Offset(0, 5)
x = x + 1
End If
Next

If x = 0 Then Exit Sub

For Each el In arr
Set c = sh2.Range("B:B").Find(el, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If c.Offset(0, 1).Value = "" Then
c.Offset(0, 1).Value = 1
Else
c.End(xlToRight).Offset(0, 1).Value = c.End(xlToRight).Value + 1
End If
Set c = sh2.Range("B:B").FindNext(c)
Loop While c.Address <> FirstAddress
End If
Next

End Sub
The logic of the macro from karmapala works on a logic suiting a slightly modified requirement description. It recognises that we have a pair of criteria resulting in having a column I value from 1.xls which needs to be looked for (matched) to a value in column B of macro.xlsm, after which the action ( to put remark 1 2 3 .. etc….. ) is taken to that row in macro.xlsm

So in Rem 2 of my version of karmapala’s macro , a one dimensional array, arr() , is made to hold those values. At the end of that code section we have this arr():
arr() of column I meeting criteria.JPG : https://imgur.com/MhEsfNm


22
25
17388
100
or
arr()={22, 25, 17388, 100}

Rem 3 then uses the .Find ( and .Find Next ) method to find the mating row in macro.xlsm , and do the action ( to put remark 1 2 3 .. etc….. ) is taken to that row in macro.xlsm

here the before
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
B
C
D
E
F
G
H
I
J
K
L
M
N

1


2
22
1
2
3
4


3
25
1
2
3


4
15083
1


5
17388
1
2
3
4
5
6
7


6
100
1
2
3
4
5
6
7
8
9
10
11


7
236
Worksheet: Sheet1 20 June excelforum

here the after
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
B
C
D
E
F
G
H
I
J
K
L
M
N

1


2
22
1
2
3
4
5


3
25
1
2
3
4


4
15083
1


5
17388
1
2
3
4
5
6
7
8


6
100
1
2
3
4
5
6
7
8
9
10
11
12


7
236
Worksheet: Sheet1 20 June excelforum

here is my version of the macro: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=14142&viewfull=1#post14142

[DocAElstein]

The formula solution from pharmacologist ( https://www.excelforum.com/excel-programming-vba-macros/1319768-if-condition-met-then-put-the-remark-between-files.html#post5353103 )

Put this in the top of column I of 1.xls and drag down and across however many columns you need.


=IF(OR(AND($D2=$E2,$D2<>$F2),AND($D2=$F2,$D2<>$E2)),IF(ISBLANK(INDEX([Macro.xlsm]Sheet1!C:C,MATCH($B2,[Macro.xlsm]Sheet1!$A:$A))),"",INDEX([Macro.xlsm]Sheet1!C:C,MATCH($B2,[Macro.xlsm]Sheet1!$A:$A))),"")
This is a nice idea, but there are a few problems.

Two minor problems:
_(i) A simple problem possibly a typo : you should not use column I of 1.xls , since that has data in. We can use any column away from the data, or we could start the formula at some convenient row down away from the data

_(ii) The formula will not work in 1.xls , since it will try to reference all the rows in a column in macro.xlsm – there are 1048576 rows in macro.xlsm, but a formula used in 1.xls will give us a problem , saying that it cannot use more than the row number for pre Excel 2007, which is 65536


Before looking further into the formula, lets simplifier/shorten/change a few names and paths to make the analysis and modification of the formula easier to follow.
I will store my files on my desktop. So the full path and file name of my files are:
C:\Users\Elston\Desktop\macro.xlsm
C:\Users\Elston\Desktop\1.xls
I will use a sheet name to help me later navigating the many duplicated / similar / cross posted questions fro the OP , Avinash. I will use
1_xlsSh1efJ20
macro_xlsmSh1efJ20 ( This is just for me to reference the workbooks as that uploaded at excelforum on June 20 ( https://www.excelforum.com/excel-programming-vba-macros/1319768-if-condition-met-then-put-the-remark-between-files.html#post5352953 )
So before the use of any formula or coding, this is the situation:
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N

1Symbol


2ACC221234


3ADANIENT25123


4ADANIPORTS150831


5ADANIPOWER173881234567


6AMARAJABAT1001234567891011


7ASIANPAINT236
Worksheet: macro_xlsmSh1efJ20

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1200
1200
1173
1194.2
1194.2
22


3NSEADANIENTEQ
141.4
143.7
141.4
141.05
141.05
25


4NSEADANIPORTSEQ
309
309
309
309.25
309.25
15083


5NSEADANIPOWEREQ
33.3
33.3
32.1
32.35
32.35
17388


6NSEAMARAJABATEQ
555
578
555
572.85
572.85
100


7NSEASIANPAINTEQ
1529
1529
1529
1552.95
1552.95
236


8
Worksheet: 1-Sheet1 20 June excelforum

My version of pharmacologist’s formula to suit the workbook and worksheets name is then initially:

=IF(OR(AND($D2=$E2;$D2<>$F2);AND($D2=$F2;$D2<>$E2));IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A:$A)));"";INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A:$A)));"")

=IF(OR(AND($D2=$E2,$D2<>$F2),AND($D2=$F2,$D2<>$E2)),IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C,MATCH($B2,[macro.xlsm]macro_xlsmSh1efJ20!$A:$A))),"",INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C,MATCH($B2,[macro.xlsm]macro_xlsmSh1efJ20!$A:$A))),"")
Note: I have given two forms. 1 is in English Excel, the other is in my German Excel , which I have change the formula default option to English from thee default German, but which still uses the ; separator rather than the English conventional ,
If I try to use this formula, in , for example, cell K2 of 1.xls , then I get the error situation mentioned in (i) associated with the row and column count:
RowsCountProblemInFormula.JPG : https://imgur.com/KZtIJ3h




In the next posts I look at corrections/ adjustments to the formula. ( Note the output required by the OP , is that produced by the working solution from ( https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=14130&viewfull=1#post14130
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=14142&viewfull=1#post14142 )
This is the solution required

_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N

1Symbol


2ACC2212345


3ADANIENT251234


4ADANIPORTS150831


5ADANIPOWER1738812345678


6AMARAJABAT100123456789101112


7ASIANPAINT236
Worksheet: macro_xlsmSh1efJ20

[DocAElstein]

row problem , (ii)
We can overcome the row problem , (ii) , by fixing the ranges used in some parts of the formula. In the practice we might know the maximum rows, or we could estimate the largest most likely. ( If we were using VBA to put the formula in, then we could dynamically determine it ( http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466 Making Lr dynamic ( using rng.End(XlUp) for a single column. ) )
For simplicity of this analysis we will limit the rows to rows 2 – 7
This formula example solves the row problem , (ii)

=IF(OR(AND($D2=$E2;$D2<>$F2);AND($D2=$F2;$D2<>$E2));IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"")

Further adjustments/Corrections
The OP actually wants the results in macro.xlsm, not 1.xls. ( As the OP did not produce a before and after, it is easy to get confused…. – half the time, this OP, Avinash doesn’t know himself what he wants )
The formula is already quite flexible in that it can be placed in any cell in 1.xls. We can make it further flexible by changing the cell references for cells in 1.xls to include the workbook and first worksheet of 1.xls, ( '[1.xls]1_xlsSh1efJ20'! ) This will then make the formula work equally well in either 1.xls or macro.xlsm

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2);AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"")

For example, I put this formula in R2 of macro.xlsm and drag it across and down. Here for example just shown 4 of the formulas: ( Note Excel has removed the workbook and worksheet referrence for 1.xls - this is just a strange habit of Excel : - Excel removes the bits of the cell referrence that it does not need ... )
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
R
S

2
=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"",INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"")
=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"",INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"")


3
=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$E3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$F3),AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$F3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"",INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"")
=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$E3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$F3),AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$F3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"",INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"")
Worksheet: macro_xlsmSh1efJ20


I get for the full range giving the following results.

_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
R
S
T
U
V
W
X
Y
Z
AA
AB
AC

2
1
2
3
4


3
1
2
3


4


5
1
2
3
4
5
6
7


6
1
2
3
4
5
6
7
8
9
10
11


7
Worksheet: macro_xlsmSh1efJ20

So it looks like the formula is “working” … but it is not giving us the correct results :
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
C
D
E
F
G
H
I
J
K
L
M
N

2
1
2
3
4
5


3
1
2
3
4


4
1


5
1
2
3
4
5
6
7
8


6
1
2
3
4
5
6
7
8
9
10
11
12


7
Worksheet: macro_xlsmSh1efJ20


We will investigate further in the next posts

[DocAElstein]

General formula construction / relation to OP requirement
It is helpful at this stage to make an initial attempt understand the formula.

We can see that we have an “outer” If , pseudo like

If ( criteria met resulting in having a column I value from 1.xls which needs to be looked for (matched) to a value in column B of macro.xlsm ,
____Then do some stuff to get the values 1 , 2 , 3 … etc ,
_______Else "" )

Here we can see the cause of the missing 1 in row 4 . This is the situation where the original data, unchanged should be present. So replacing "" with a reference to the original data , [macro.xlsm]macro_xlsmSh1efJ20!C2, should overcome this problem.
However, we will see a small problem that across the row we will have 0s instead of nothing, since , by default, Excel is evaluating like “nothing” number, which is zero
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
R
S
T
U

4
1
0
0
0
Worksheet: macro_xlsmSh1efJ20

This can be overcome with a trick to make it show “nothing text” by adding a &"" to the cell value, [macro.xlsm]macro_xlsmSh1efJ20!C2&""

So finally we have

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2);AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))) ;"";INDEX(macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))) ;[macro.xlsm]macro_xlsmSh1efJ20!C2&"")

We have now solved the problem to put data in unchanged for the unmatched case. We need to modify somehow the first part of the outer If to get the correct results in.
We need to have values of 1 greater than the found values , and offset by one to the right

This part of the formula would normally, for the first cell return us the value of 1
INDEX(macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))
This part actually is present twice in an If, pseudo
If ( This is blank , then "" , else This )

I can see a way to do this to get the results for all but the first column, as follows: We would look for a match in the previous column and the result would be added by 1

For example, this formula…_

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2);AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))) ;"";INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+ 1);C2&"")
_.. almost gives us what we want, for example in the second row
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
R
S
T
U
V
W

2
23
2
3
4
5

Worksheet: macro_xlsmSh1efJ20

One way to get the first column correct, could be to have a check on the part giving the result to see if the column first column is under consideration , in which case , we then need again to see if it is empty ( If(C2="";"";1) ) in which case a "" will be returned, else a 1

If(Column(C2)=3;If(C2="";"";1);INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+ 1)

Finally, a working formula seems to be ( For in , for example R2 and copied across and down ) ,
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
R

2
=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!B$2:B$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"",IF(COLUMN(C2)=3,IF(C2="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+ 1)),C2&"")
Worksheet: macro_xlsmSh1efJ20

[DocAElstein]

Some other final notes to formula solution

The formula from pharmacologist seems to be matching columns A in macro.xlsm to B in 1.xls. The OP wanted columns B in macro.xlsm to I in 1.xls. But I will ignore that for now.

The final formula version of mine will also work if the workbook, 1.xls , is closed.
To verify and get the formula for that, simply close 1.xls and observe the formula changed automatically by Excel

=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2; 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2);AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2; 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!B$2:B$7;MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))) ;"";IF(COLUMN(C2)=3;IF(C2="";"";1);INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('C:\Use rs\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+ 1));C2&"")
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
R
S

2
=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"",IF(COLUMN(C2)=3,IF(C2="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Use rs\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+ 1)),C2&"")
=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"",IF(COLUMN(D2)=3,IF(D2="","",1),INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Use rs\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+ 1)),D2&"")


3
=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"",IF(COLUMN(C3)=3,IF(C3="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Use rs\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))+ 1)),C3&"")
=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh 1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))) ,"",IF(COLUMN(D3)=3,IF(D3="","",1),INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Use rs\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))+ 1)),D3&"")
Worksheet: macro_xlsmSh1efJ20


macro.xlsm : https://app.box.com/s/z358r7tbc9hzthi539dlj49jsf4gyg8p
1.xls : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk

[DocAElstein]

Spare post for later use

[DocAElstein]

The next version.....

Sub test() ' https://www.excelforum.com/excel-programming-vba-macros/1321581-put-data-in-series-by-vba.html#post5363303
Dim r As Range, x, BK As String, myVal
BK = "'" & ThisWorkbook.Path & "\[1(sample).xls]1-Sheet1'!"
With Sheets("sheet1")
For Each r In .Range("b2", .Range("b" & Rows.Count).End(xlUp))
myVal = r.Value
If Not IsNumeric(myVal) Then myVal = Chr(34) & myVal & Chr(34)
x = ExecuteExcel4Macro("match(" & r.Value & "," & BK & "r1c9:r10000c9,0)")
If IsNumeric(x) Then
With .Cells(r.Row, Columns.Count).End(xlToLeft)
.Cells(1, 2) = .Value + 1
End With
End If
Next
End With
End Sub
_... to be looked at later
( Note Yasser found it intersting ... https://eileenslounge.com/viewtopic.php?f=30&t=34952 )




Share '1(sample) excelforum 11july.xls' : https://app.box.com/s/q44t3dglvoq59ozvyi4c411ekw0aox9s
Share 'H2(SAMPLE) excelforum 11July.xlsb' : https://app.box.com/s/ziudkeb2bdmdosxhcrcx6g243sjn7n7x

[DocAElstein]

Spare post for later use

[Molly Brennholz]

if you can do it, Akan. so can I .... a spare post, i think I might need later
:)
Molly
x

[fixer]

Hello, Need help regarding the macro

Sub STEP29()
Dim wb1 As Workbook, wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
Set wb2 = ThisWorkbook
Set Ws1 = wb1.Worksheets.Item(1): Set Ws2 = wb2.Worksheets.Item(1)
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value
Let arr2() = Ws2.Range("A1").CurrentRegion.Value
ReDim arr3(0 To UBound(arr2(), 1))
Dim Cnt
For Cnt = 2 To UBound(arr2(), 1)
Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column
Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value
Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2
Dim mtchRes As Variant
Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0)
If IsError(mtchRes) Then
' a match was not found, so we do not need to remove the 1 2 3 etc...
Else
' a match was found, so we need to remove the 1 2 3 etc...
Dim Empt As Long
For Empt = 3 To UBound(arr3(Cnt - 1), 2)
Let arr3(Cnt - 1)(1, Empt) = ""
Next Empt
End If
'3c) Paste out row
Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1)
Next Cnt
wb1.Close
wb2.Save




End Sub



















Public Function CL(ByVal lclm As Long) As String
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function




Actually i converted that Alert..csv file to Alert..xls
So I need the changes in the macro according to that
So Plz have a look and help me in solving this problem Sir

[Molly Brennholz]

Hi,
I am not quite sure what you are asking…
( Your macro above seems similar to some which I did for you ( https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc ) )

Probably all you would need to do is use the File name of the File that you are using
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..xls")
But that is obvious. So possibly you are trying to ask something else.

( Of course, if you have been .Opening .csv text files with an Excel object, as in your macro above in post #1, ( https://excelfox.com/forum/showthread.php/2564-Macro-Modification?p=14144&viewfull=1#post14144 ) then you may have got sometimes problems.
But I am not going to make any attempt to explain that to you, since I can see from your recent posts here and elsewhere that many people have tried very hard to explain that all to you, and you still don’t seem to have understood anything !!! )
Molly

[fixer]

Sorry let me explain once again Molly Mam



This macro is made for alert..csv file


Sub STEP29()
Dim wb1 As Workbook, wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
Set wb2 = ThisWorkbook
Set Ws1 = wb1.Worksheets.Item(1): Set Ws2 = wb2.Worksheets.Item(1)
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value
Let arr2() = Ws2.Range("A1").CurrentRegion.Value
ReDim arr3(0 To UBound(arr2(), 1))
Dim Cnt
For Cnt = 2 To UBound(arr2(), 1)
Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column
Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value
Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2
Dim mtchRes As Variant
Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0)
If IsError(mtchRes) Then
' a match was not found, so we do not need to remove the 1 2 3 etc...
Else
' a match was found, so we need to remove the 1 2 3 etc...
Dim Empt As Long
For Empt = 3 To UBound(arr3(Cnt - 1), 2)
Let arr3(Cnt - 1)(1, Empt) = ""
Next Empt
End If
'3c) Paste out row
Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1)
Next Cnt
wb1.Close
wb2.Save




End Sub



















Public Function CL(ByVal lclm As Long) As String
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function



But now alert..csv file is replaced with alert.xls
so the macro would be


Sub STEP29()
Dim wb1 As Workbook, wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert.xls")
Set wb2 = ThisWorkbook
Set Ws1 = wb1.Worksheets.Item(1): Set Ws2 = wb2.Worksheets.Item(1)
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value
Let arr2() = Ws2.Range("A1").CurrentRegion.Value
ReDim arr3(0 To UBound(arr2(), 1))
Dim Cnt
For Cnt = 2 To UBound(arr2(), 1)
Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column
Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value
Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2
Dim mtchRes As Variant
Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0)
If IsError(mtchRes) Then
' a match was not found, so we do not need to remove the 1 2 3 etc...
Else
' a match was found, so we need to remove the 1 2 3 etc...
Dim Empt As Long
For Empt = 3 To UBound(arr3(Cnt - 1), 2)
Let arr3(Cnt - 1)(1, Empt) = ""
Next Empt
End If
'3c) Paste out row
Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1)
Next Cnt
wb1.Close
wb2.Save




End Sub



















Public Function CL(ByVal lclm As Long) As String
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function




Any more changes is required in this code then plz let me know Molly Mam

[Molly Brennholz]

I think I understood already what you are asking.
I think you are still confused and mixed up because you have still not understood anything about the difference between Excel files and text files.
You sometimes tell us that you understand. But all your questions suggest to us that you still have not understood anything at all.

If I try to explain again, you will probably just get more confused or, more likely, you will not read or understand anything I tell you. So it will just waste all our time again.




...This macro is made for alert..csv file...
Are you sure?
I do not think so.
Who made the macro for alert..csv file?

I did not make any macro for you to work on a text ( .csv ) file.
Who made this macro for you?
It looks very similar to macros which I made for you. But all the macros I made for you were for Excel files. I never made any macros for you to work with text files



If you are lucky,

Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert.xls")
may sometimes do the same as what you want

Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
to do.

So if you are lucky, that will be the only change that you need.

But it is very dangerous and stupid to ever do
Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
( That code line is using the Excel object to .Open a text file. Excel is not really supposed to do that. Excel is mostly supposed to open Excel files. But Excel will allow you to try to open a text file. Excel will try to guess what to do. It may try to do what you want. Sometimes it may do different things. Many people have tried very hard to explain that in detail to you for 2 months already).


That code line may sometimes do what you want. But there will probably always be problems.
You seem to have decided not to want to know anything about such problems. So it may be impossible for anyone to really help you anymore.




Remember one last very important thing:
You are not the only person that understands nothing about the difference between Excel files and text files.
Lots of people understand nothing about the difference between Excel files and text files.

So if you continually cross post and ask the same question at different places, you will eventually find someone that will say to you.
…Yes, that is the only change needed .
So you will be happy then. But it is the wrong answer.

But that is your choice. You should do what you want to. You prefer a simple short answer, even if it is the wrong answer.

It is nice to have different people in this world. Stay as you want to be. (You will never achieve your aim, unless your aim is to fail and get the wrong results sometimes).
But you should stay as you want to be. The world would be very boring if we all were the same.
:)

Good luck in your future failures!

Molly

[fixer]

I made a Mistake & I corrected My Mistake
I am not working with any .csv file
& In Future I will never Ask any question related to .csv file
Thnx Alot Molly Mam Doubts & Problem both are solved

[fixer]

Thnx Alot For Providing the Great Info Doc Sir & Molly Mam
Doc Sir Chill
Every Code is working Perfectly
I have no doubts in any Macro

[fixer]

Moderator Notice: This and the next 4 posts were posted here in the wrong place. I have left the copies here since the linked post has been referrenced already. I have also copied the posts to the correct place:
https://excelfox.com/forum/showthread.php/2349-Copy-and-Paste-based-on-comparisons-Match-and-calculations-of-cells-in-two-workbooks?p=14580&viewfull=1#post14580





Sub STEP6()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Wb1 As Workbook, Wb2 As Workbook
Dim r2&, lr&, i&

Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Ws1 = Wb1.Worksheets.Item(1)
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\AlertCodes.xl sx")
Set Ws2 = Wb2.Worksheets.Item(4)
With Ws1
lr = .Cells(.Rows.Count, "I").End(xlUp).Row
For i = 2 To lr
' Reset r2
r2 = 0
' Avoid error messages
On Error Resume Next
' Try to get r2
r2 = WorksheetFunction.Match(.Cells(i, "I"), Ws2.[B:B], 0)
' Restore error handling
On Error GoTo 0
' Only set column K if r2 is valid
If r2 > 0 Then
If Ws2.Cells(r2, "D") = ">" Then
.Cells(i, "K").Value = .Cells(i, "D").Value - 0.01 * .Cells(i, "D").Value
Else
.Cells(i, "K").Value = .Cells(i, "D").Value + 0.01 * .Cells(i, "D").Value
End If
End If
Next i
End With
Wb1.Save
Wb1.Close
Wb2.Close

End Sub


This codes calculate 1% of column of column D of 1.xls
but what i wanted is instead of column D, it should calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx and paste the result to same place where the current macro is putting,rest everything will be same

Kindly note AlertCodes.xlsx doesn't have headers so keep a eye on the same

Thnx for the Help

https://eileenslounge.com/viewtopic.php?f=30&t=34953&p=271443#p271443
https://chandoo.org/forum/threads/vba-code-for-correction.44637/

[DocAElstein]

Moderator Notice: This post was posted here in the wrong place. I have left the copy here since the linked post has been referrenced already. I have also copied this and the related reply posts to the correct place, starting here:
https://excelfox.com/forum/showthread.php/2349-Copy-and-Paste-based-on-comparisons-Match-and-calculations-of-cells-in-two-workbooks?p=14580&viewfull=1#post14580




You almost had it right here: https://eileenslounge.com/viewtopic.php?p=271427&sid=ccc11b24baced575ceee4002b96a1745#p271427
You were close to correct.

I try to explain again what Hans tried to explain ( I think you almost understood ):
__With Ws1
………
……….
…………………. Cells(i, "E") ……… .Cells(i, "E")
__End With
is almost =
.....Ws1.Cells(i, "E") …… Ws1.Cells(i, "E")

You want Ws2 ( for Alertcodes.xlsx ) , not Ws1

Maybe like


Wtih Ws1
If Ws2.Cells(r2, "D") = ">" Then 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx
.Cells(i, "K").Value = Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
Else
.Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
End If
End With
This will also be the same


If Ws2.Cells(r2, "D") = ">" Then 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx
Ws1.Cells(i, "K").Value = Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
Else
Ws1.Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
End If
If this does not work, then you must explain again exactly what you want the macro to do.

Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
and
Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
is 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx



This was wrong:
Ws2.Cells(i, "E").Value - 0.01 *.Cells(i, "E").Value
and
Ws2.Cells(i, "E").Value + 0.01 * .Cells(i, "E").Value
It is 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of 1.xls
It is the same as
Ws2.Cells(i, "E").Value - 0.01 *Ws1.Cells(i, "E").Value
and
Ws2.Cells(i, "E").Value + 0.01 * Ws1.Cells(i, "E").Value


( I never use
__ With
__ End With
because it confuses me.
But lots of other people, like Hans do use it. Everyone writes codes a bit differently, because all VBA codes can be written in many ways. )




Also
Have you seen this ? : https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14565#post14565
Your macro , Sub STEP6() from here
https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14562&viewfull=1#post14562
and here
https://eileenslounge.com/viewtopic.php?p=271385#p271385
has the wrong Lr2

[fixer]

Moderator Notice: This post was posted here in the wrong place. I have left the copy here since the linked post has been referrenced already. I have also copied this and the related reply posts to the correct place, starting here:
https://excelfox.com/forum/showthread.php/2349-Copy-and-Paste-based-on-comparisons-Match-and-calculations-of-cells-in-two-workbooks?p=14580&viewfull=1#post14580




Thnx Alot Soc Sir for helping me in solving this problem Sir
Problem Solved
Have a Great Day

[DocAElstein]

Moderator Notice: This post was posted here in the wrong place. I have left the copy here since the linked post has been referrenced already. I have also copied this and the related reply posts to the correct place, starting here:
https://excelfox.com/forum/showthread.php/2349-Copy-and-Paste-based-on-comparisons-Match-and-calculations-of-cells-in-two-workbooks?p=14580&viewfull=1#post14580





No Doc Sir this problem is not solved yet
i am sending the sample file with output in next 10 min



.....

No Doc Sir this problem is not solved yet
i am sending the sample file with output in next 10 min

See here https://excelfox.com/forum/showthread.php/2349-Copy-and-Paste-based-on-comparisons-Match-and-calculations-of-cells-in-two-workbooks?p=14584&viewfull=1#post14584
( Question moved to here: https://excelfox.com/forum/showthread.php/2349-Copy-and-Paste-based-on-comparisons-Match-and-calculations-of-cells-in-two-workbooks?p=14578&viewfull=1#post14578 )

[DocAElstein]

Test post for later use, to get url now

[DocAElstein]

In Avinash’s world are two types of files. Only two types , Text files and Excel files. There are no other types of file. There will probably never be any other types of files.


All Files , text files, excel files and all other file types , are held in a computer as just a long string of Text. Even an Excel File is just a long piece of text inside a computer. But it is hard to understand. The software that is Excel decodes the long text and tries to make it appear in values and formats that we can see in a worksheet




Excel Files ( Excel Worksheet spreadsheet )
.xls .xlsm .xlsx .xlsb
An Excel file is very complicated. It can have values and lots of cell formatting.
Because of all cell formatting, it can be very slow in working. Excel is not an efficient thing to use if you only have values

We can open an Excel File manually, using Excel or Notepad
ExcelFileOpenInNotepad.JPG : https://imgur.com/bdym9Lc ExcelFileOpenInExcel.JPG : https://imgur.com/gwOtksS
32983296
In Excel it may looks like this
ExcelFile.JPG : https://imgur.com/8xaZihR
3297
_____ Workbook: ExcelFile.xlsx ( Using Excel 2007 32 bit )
Row\ColABC
1AB

2CD

3
Worksheet: Sheet1

In Notepad it looks like this:
ExcelFileInNotepad.JPG : https://imgur.com/wHTPbO6
3295

PK ! U6»+w ( Ø [Content_Types].xml ¢Ô ( ÌTËNÃ0 ¼#ñ ‘¯(q[ B¨i <ŽP ø co «ŽmyÝÒþ=›¤* Ѩ¥=pI %;3žÌp¼¬L²€€ÚÙœõ³ KÀJ§´æìíõ1½a Fa•0ÎBÎV€l<:? ¾®<`BÓ sVÆèo9GYB%0s ,½)\¨D¤Ç0å^È™˜ -ôz×\: ÁÆ4Ö l4|& A+H&"Ä'Q _ Úk?#<–ܵƒ5w΄÷FK I9_Xõƒ5uE¡%('ç qe ØEÂw b\ À£©Ð
K€X™¬ Ý0ßC!æ&& Kr 5=€ÁÃŽ¶63£ÉæøXj
ÝÞu{òáÂìݹ٩]©ÝÉ*¡íF÷Ž Ôü•È›ÛÕ‰…lñ»tPŠ&Áy䔹£ù¡^½ •z‚„ 5lw•§ƒ£5|è^ Ð ¹ýn½‹Ë¢ãøvø›¬XŠ ê% jË“×ÆWì½²)]€Ã ²é’zú—Dò¦çGŸ ÿÿ PK ! µU0#õ L
Î _rels/.rels ¢Ê (
That text above of a simple Excel File is very complicated because it has all the information needed by Excel to make all the cells and formatting

Making an Excel File with Excel VBA
We can make that Excel File using Excel VBA

Sub MakeExcelFile()
Dim Wb As Workbook, Ws As Worksheet
Workbooks.Add
Set Wb = ActiveWorkbook
Set Ws = Wb.Worksheets.Item(1)
Let Ws.Range("A1") = "A": Let Ws.Range("B1") = "B"
Let Ws.Range("A2") = "C": Let Ws.Range("B2") = "D"
Wb.SaveAs Filename:=ThisWorkbook.Path & "\ExcelFile.xlsx"
End Sub


We usually open Excel files with Excel. So that is why the files with the extensions of .xls .xlsm .xlsx .xlsb are called Excel Files. Such files were designed to be opened in Excel

If we are using an Excel file to store simple data values, then the values are usually divided up so that when opened in Excel the data is shown in cells in rows and columns



Text Files
.csv .txt
Text files are very simple. They only have values and sometimes , if it is being used to store data values, it may have separators**. ( Sometimes we call the seperators delimiters ).

A B
C D

A,B
C,D

We usually open text files with a text editor. For example Notepad.
TextFileOpenInNotepad.JPG : https://imgur.com/zzRAVIN
3299
Because Text files are not complicated, we can see them easily in Notepad. Because we sometimes open files with the extension of .csv .txt in Notepad , they may be called Notepad files, and sometimes files with the extension of .csv may be called a comma separated values text values file or “csv file”. But really they are both Text files

**If we want to store simple data values in a text file, then we have the problem that we have no way to make the data appear in cells, because a text file has no cell information and also no other formatting information.
So we typically separate data on a line with something like, _ ; , vbTab | _ etc…
A Line is separated from the next line by an “invisible” character which tells a computer to make a new line, for example
vbCr & vbLf

Make a Text file using Excel VBA
We can make a text file using Excel VBA

Sub MakeTextFile()
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Open ThisWorkbook.Path & "\" & "TextFile.txt" For Output As #FileNum ' Will be made if not there
Print #FileNum, "A" & vbTab & "B" & vbCr & vbLf & "C" & vbTab & "D"
Close #FileNum
End Sub

We can try to .Open a text file in Excel. Excel will try to show us the values from it in cells. It may work. It may not work. There will always be problems.
But we may want to see the values in Excel
Because there are always problems .Opening a text file in Excel, we import the values into an Excel worksheet instead
The text file made in that last macro , TextFile.txt , can be seen in Notepad to look like this:
TextFile_txtInNotepad.JPG : https://imgur.com/0B2BQpK

A B
C D
( We can represent that file as a simple string in VBA coding, thus:
"A" & vbTab & "B" & vbCr & vbLf & "C" & vbTab & "D"
( We might sometimes call this a Tab separated values or Tab delimited values text file ) )
The following macro is the best way to put the values from that text file into a worksheet. This may typically be called importing a text file into Excel. It does not convert a text file to an Excel File.

Sub ImportTextFileValuesIntoExcelWorksheet()
Rem 1 Add a workbook to display the values from a Tab delimited values text file
Dim Wb As Workbook, Ws As Worksheet
Workbooks.Add
Set Wb = ActiveWorkbook
Set Ws = Wb.Worksheets.Item(1)

Rem 2 Put the entire text file into a single string, TotalFile
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
Let PathAndFileName = ThisWorkbook.Path & "\" & "TextFile.txt" '
Open PathAndFileName For Binary As #FileNum ' Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum ' I need to do this, or there may be problems with my computer as I have an open route which may interact badly with something else

If Right(TotalFile, 2) = vbCr & vbLf Then Let TotalFile = Left(TotalFile, Len(TotalFile) - 2) ' Sometimes an extra line seperator gets added, so I remove it if that is the case

Rem 3 Loop through the lines of the text file and paste each line to a row in the worksheet
Dim Rws() As String ' I want to get an array of all my rows
Let Rws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) ' I Split by the row seperator which is usualy vbCr & vbLf but note that it might sometimes be something else
Dim Cnt As Long ' I wat to loop for all the lines in the text file
For Cnt = 1 To UBound(Rws()) + 1 ' I need to use +1 because the one dimensional array returned by Split starts at 0
Dim Clms() As String ' I want an array of just the values. I can easily achieve this by spliting by the value seperator
Let Clms() = Split(Rws(Cnt - 1), vbTab, -1, vbBinaryCompare) ' I now split the row into columns using the value seperator, which in this case is vbTab
Let Ws.Range("A" & Cnt & "").Resize(1, UBound(Clms()) + 1).Value = Clms() ' I can assign my 1 dimensional array to a worksheet range, and Excel will accept it conventionally as a row of data
Next Cnt
End Sub

It is better to use text files and manipulate text files with Excel VBA if we are only looking at values



VBA Arrays
It is better to use text files and manipulate text files with Excel VBA if we are only looking at values.
Excel is very slow and inefficient if we are only looking at values
But we can make Macros for Excel using Excel VBA a little better if we use VBA arrays.
Instead of putting values in an Excel worksheet, one value at a time, we can put all values into an array, and then at the end of the macro we can put all the values into the worksheet in one go. This makes the macro quicker

Sub MakeExcelFileUsingVBAArrays()
Dim Wb As Workbook, Ws As Worksheet
Workbooks.Add
Set Wb = ActiveWorkbook
Set Ws = Wb.Worksheets.Item(1)
' Make array
Dim arr1(1 To 2, 1 To 2) As String
Let arr1(1, 1) = "A": Let arr1(1, 2) = "B"
Let arr1(2, 1) = "C": Let arr1(2, 2) = "D"
' Paste entire array into worksheet in one go
Let Ws.Range("A1:B2").Value = arr1()

Wb.SaveAs Filename:=ThisWorkbook.Path & "\ExcelFileMadeUsingVBAArrays.xlsx"
End Sub

The array, arr1() , can be considered to look like this:



1
2


1
A
B


2
C
D

But we cannot easily see this array, as it is just inside the computer in memory. But we can paste the array into a worksheet in one go using a code line like:
Let1 Ws.Range("A1:B2").Value = arr1()



For Avinash it is better to use as much manipulation of text files using VBA and VBA arrays as possible

You must not learn any VBA coding if you do not want to.
But you must try to understand the difference in text files and excel files
If you cannot or will not learn this, then there is no point in anyone trying to help you further. You will get nowhere. You will waste everybody’s time, including your own

[DocAElstein]

Here’s a few of the identical cross posts on a new question.
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)
https://www.mrexcel.com/board/threads/clear-the-data-else-put-numbers-in-series-by-vba.1141539/
https://www.excelguru.ca/forums/showthread.php?10748-Clear-the-data-Else-put-numbers-in-series
https://www.myonlinetraininghub.com/excel-forum/vba-macros/clear-the-data-else-put-numbers-in-series
https://www.experts-exchange.com/questions/29189773/Condition-Met-then-Remarks-in-Numerical-Series-by-vba.html
https://www.excelforum.com/excel-programming-vba-macros/1323098-condition-met-then-remarks-in-numerical-series-by-vba.html#post5371511 ( Closed by Jeff in error )
http://www.eileenslounge.com/viewtopic.php?f=30&t=35053

[DocAElstein]

posting to get the URL for later use

[DocAElstein]

bmmnbmnb

[DocAElstein]

bmmnbmnb

[DocAElstein]

hlkhkhkh

[DocAElstein]

Old one fucked up by Avinash’s description mess up
https://www.excelforum.com/excel-programming-vba-macros/1322316-condition-matches-then-clear-the-data-else-put-the-remark-in-series-by-vba.html#post5370608
there are 2 files 1.xls & macro.xlsm (macro will be placed in macro.xlsm),both files are loacted in diffrent 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 equal to column E of 1.xls & column H of 1.xls is lower than column K then match column I of 1.xls with column B of macro.xlsm & if it matches then clear the data in that row ELSE put the remark
If column D of 1.xls equal to column F of 1.xls & column H of 1.xls is greater than column K then match column I of 1.xls with column B of macro.xlsm & if it matches then clear the data in that row ELSE put the remark

[DocAElstein]

Using new ( old ) account "Zaeguzah "
https://www.excelforum.com/excel-programming-vba-macros/1323944-conditon-met-then-paste-the-data.html


Hello Guys,
Looking for a macro

If column B of 1.xls matches with column A of Book1.xlsb & the difference between Column D of 1.xls & Column E of 1.xls is not greater than 0.50% of column D of 1.xls then put Remarks Else Clear the data from Column B of book1.xlsb

Note:
Macro will be placed in a book1.xlsb
Sheet name can be anything
Remarks means If condition met then finding the last row of book1.xlsb and add +1 to the data in the last row & put th result in next cell in that row
clear data means if condition doesnt met then clear all the data in book1.xlsb in that row
Plz see the output (it is the output of the macro)

[DocAElstein]

hlkhlhh

[fixer]

Hi,
I am looking for a macro that will do the things mentioned below

If column E of sheet3 of 3.xlsm matches with sheet4 of Column B of 10.xlsx then match the same with column B of sheet1 of 3.xlsm & if matches then put the numerical data in series by vba in sheet1 of 3.xlsm

Macro will be placed in 3.xlsm file


Thnx Alot
https://www.excelforum.com/excel-programming-vba-macros/1326195-match-data-between-sheet-and-pasting-the-output-by-adding-1-a.html

[fixer]

Probelm Solved
Thnx Alot

[DocAElstein]

Probelm Solved..
You forgot ( again ) to tell us all how it was solved it and where you got the solution...