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

Printable View

Show 40 post(s) from this thread on one page

03-17-2020, 09:56 PM
fixer

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

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/showthrea...-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/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
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=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
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=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=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
03-17-2020, 09:56 PM
fixer

1 Attachment(s)

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

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/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9iHOYYpaAbC
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=Ugw3nF0C04AGt73H1BB4AaABAg. 9h6VhNCM-DZ9h7EqbG23kg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg. 9h4sd6Vs4qE9h7KvJXmK8o
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=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79hNGvJbu
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79YAfa24T
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79M1SYH1E
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78SxhXTnR
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
03-17-2020, 09:56 PM
fixer

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

This post is a … Moderator Post Copy….

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

https://excelfox.com/forum/showthrea...ll=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/showthrea...ll=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/showthrea...ll=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/showthrea...ll=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/showthrea...ll=1#post14138
https://www.excelforum.com/excel-pro...een-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/showthrea...ll=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/thread...y-vba.1141539/
https://www.excelguru.ca/forums/show...bers-in-series
https://www.myonlinetraininghub.com/...bers-in-series
https://www.experts-exchange.com/que...es-by-vba.html
https://www.excelforum.com/excel-pro...ml#post5371511 ( Closed by Jeff in error )
http://www.eileenslounge.com/viewtopic.php?f=30&t=35053

https://www.excelforum.com/excel-pro...ml#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/showthrea...ll=1#post14745
https://www.excelforum.com/excel-pro...-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-pro...ml#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-pro...dding-1-a.html
https://excelfox.com/forum/showthrea...ll=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
03-20-2020, 03:53 PM
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/showth...ll=1#post12837

So Try this data : http://www.excelfox.com/forum/showth...ll=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

1
Symbol

2
ACC
1

2

3

3
ADANIENT
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/showth...ll=1#post12840
(You also need Function CL( ) )

Molly
03-20-2020, 06:04 PM
fixer

Code:

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

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg. A0opm95t2XEA0q3KshmuuY
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
03-20-2020, 06:45 PM
Molly Brennholz

Quote:

Originally Posted by 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/showth...ll=1#post12837 ....

Quote:

Originally Posted by fixer

...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
03-20-2020, 08:47 PM
fixer

1 Attachment(s)

Code:

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
03-20-2020, 09:05 PM
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.
03-20-2020, 09:17 PM
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

Code:

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:

Quote:

Originally Posted by fixer

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
03-21-2020, 12:38 AM
fixer

1 Attachment(s)

Plz see the sample file doc sir

Show 40 post(s) from this thread on one page

_Row\^Col	A	B	C	D
1	Symbol
2	ACC	1	2	3
3	ADANIENT	1
4