Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc) TEST COPY

**DocAElstein** · 09-11-2019, 05:03 PM

Some further notes and information for this Thread:
http://www.excelfox.com/forum/showth...1476#post11476
http://www.excelfox.com/forum/showth...ulation-by-vba
https://www.mrexcel.com/forum/excel-...ation-vba.html
http://www.vbaexpress.com/forum/show...Formula-by-vba
https://www.excelforum.com/excel-pro...on-by-vba.html

There are many different was to achieve the same…
Some further notes on changes that can be made to Sub Vixer9a() from here: http://www.excelfox.com/forum/showth...ll=1#post11475

b) Rng.Value = Rng.Value

Excel VBA has been written such that applying .Value to a cell or cells has a similar effect to writing manually into a cell.

So if you do this:
Range("A1").Value = "X"
, it will write
X
in the first cell.
You will then see in the first cell this
X

If you do this:
Range("A1").Value = " = 1"
, it will write
= 1
in the first cell.
But, you will see in the first cell just the Value:
1

To explain:
The .Value Property of a range object , for example a single cell, is what we "see" in the cell. But if you apply .Value to a cell it will write into the cell as if you did it manually.

So, in your code you can replace this: …_

Code:

'3(iii) I need only result in the cell no formulas
 Ws1.Range("D2:D" & Lr1 & "").Copy
 Ws1.Range("D2:D" & Lr1 & "").PasteSpecial Paste:=xlPasteValues
 Let Application.CutCopyMode = False

_.. with this:

Code:

'3(iii) I need only result in the cell no formulas
 Let Ws1.Range("D2:D" & Lr1 & "").Value = Ws1.Range("D2:D" & Lr1 & "").Value

Code:

Sub Vixer9b() ' demo for   rng.Value = rng.Value
Rem 1 Workbook and worksheets info
'1a) Workbook info
' Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
Dim Wb1 As Workbook ' This will be set later when the workbook is opened
Dim MyPath As String: Let MyPath = "C:\Users\sk\Desktop" '  ".....The file will be located in C:\Users\sk\Desktop ....
Dim strWb1 As String: Let strWb1 = "sample.xlsx" '                                                          " ....and file name is sample.xlsx
'1b) Worksheets info
Dim Ws1 As Worksheet ' This will be set later when the workbook is opened)
Dim Lr1 As Long '      Let Lr1 = 10 for sample file  , but we will determine it dynamically after opening the file
Rem 2 Open file   "..... file is not opened so we have to open the file by vba
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\sample.xlsx"
 Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1  '  ...both files are located in same place
' Workbooks.Open Filename:=MyPath & "\" & strWb1              '  ...file will be located in C:\Users\sk\Desktop
 Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
 Set Ws1 = Wb1.Worksheets.Item(1)

' make Lr1 dynamic .... http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11474&viewfull=1#post11474
 Let Lr1 = Ws1.Range("C" & Ws1.Rows.Count).End(xlUp).Row
Rem 3 The Process ..."....
'3(i) ....Multiply the value of B2 by 1.5%, then multiply that result by 56 and then paste the result in D2.. formula will be added by me in the code, put that formula in
 Ws1.Range("D2").Value = "=B2*(1.5/100)*56"
'3(ii) ....drag it
 Ws1.Range("D2").AutoFill Destination:=Ws1.Range("D2:D" & Lr1 & ""), Type:=xlFillDefault
'3(iii) I need only result in the cell no formulas
 Let Ws1.Range("D2:D" & Lr1 & "").Value = Ws1.Range("D2:D" & Lr1 & "").Value
Rem 4 save it and close it
 Wb1.Save
 Wb1.Close
End Sub

_c) Apply "fixed vector"** form across a range

I can apply the formula in its "fixed vector"** form across a range. In other words I can apply the same formula in its fixed vector form across a range. Applying the same fixed vector formula across a range will make any referred to cells change the shown formula appropriately to apply to the different cells

** In simplified terms, "fixed vector", means notation without the $. So..
A1 is "fixed vector"
$A$1 is absolute referencing

So we can replace this:

Code:

'3(i) ....Multiply the value of B2 by 1.5%, then multiply that result by 56 and then paste the result in D2.. formula will be added by me in the code, put that formula in
 Ws1.Range("D2").Value = "=B2*(1.5/100)*56"
'3(ii) ....drag it
 Ws1.Range("D2").AutoFill Destination:=Ws1.Range("D2:D" & Lr1 & ""), Type:=xlFillDefault

With this:

Code:

'3(i)(ii) ....Multiply the value of B2 by 1.5%, then multiply that result by 56 and then paste the result in D2..   ....drag it formula will be added by me in the code, put that formula in
 Ws1.Range("D2:D" & Lr1 & "").Value = "=B2*(1.5/100)*56"

Code:

Sub Vixer9c() ' demo for   fixed vector applied across a range
Rem 1 Workbook and worksheets info
'1a) Workbook info
' Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
Dim Wb1 As Workbook ' This will be set later when the workbook is opened
Dim MyPath As String: Let MyPath = "C:\Users\sk\Desktop" '  ".....The file will be located in C:\Users\sk\Desktop ....
Dim strWb1 As String: Let strWb1 = "sample.xlsx" '                                                          " ....and file name is sample.xlsx
'1b) Worksheets info
Dim Ws1 As Worksheet ' This will be set later when the workbook is opened)
Dim Lr1 As Long '      Let Lr1 = 10 for sample file  , but we will determine it dynamically after opening the file
Rem 2 Open file   "..... file is not opened so we have to open the file by vba
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\sample.xlsx"
 Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1  '  ...both files are located in same place
' Workbooks.Open Filename:=MyPath & "\" & strWb1              '  ...file will be located in C:\Users\sk\Desktop
 Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
 Set Ws1 = Wb1.Worksheets.Item(1)

' make Lr1 dynamic .... http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11474&viewfull=1#post11474
 Let Lr1 = Ws1.Range("C" & Ws1.Rows.Count).End(xlUp).Row
Rem 3 The Process ..."....
'3(i)(ii) ....Multiply the value of B2 by 1.5%, then multiply that result by 56 and then paste the result in D2..   ....drag it formula will be added by me in the code, put that formula in
 Ws1.Range("D2:D" & Lr1 & "").Value = "=B2*(1.5/100)*56"
'3(iii) I need only result in the cell no formulas
 Let Ws1.Range("D2:D" & Lr1 & "").Value = Ws1.Range("D2:D" & Lr1 & "").Value
Rem 4 save it and close it
 Wb1.Save
 Wb1.Close
End Sub

_d) Internal calculation with VBA arrays
We do not need to put formulas into any cells.
We can do the calculations internally, within coding, and then paste all the values out in one go.
Using VBA arrays is a convenient way to do this.

_a) First we bring all the data into an array.
_b) Then we do the calculations
_c) Finally we paste out all the calculated values in one go

We can replace all of Rem3 with new coding
Rem 3 The Process .. using VBA arrays
'3_a) First we bring all the data into an array.
'3_b) Now we do the calculations
'3_c) Finally we paste out all the calculated values in one go

Code:

Rem 3 The Process ...using VBA arrays
'3_a) First we bring all the data into an array. (We also take in the column D values, even if the column D is empty)
Dim arrDta() As Variant
 Let arrDta() = Ws1.Range("A1:D" & Lr1 & "").Value
'3_b) Now we do the calculations looping through the row data held internally in the data array, arrDta()
Dim Cnt As Long
    For Cnt = 2 To Lr1
     Let arrDta(Cnt, 4) = arrDta(Cnt, 2) * (1.5 / 100) * 56 ' .. like.. column "D" = column "B" * (1.5/100) * 56
    Next Cnt
'3_c) Finally we paste out all the calculated values ( and also the original data )  in one go
 Ws1.Range("A1:D" & Lr1 & "").Value = arrDta()

Code:

Sub Vixer9d() ' demo using VBA arrays
Rem 1 Workbook and worksheets info
'1a) Workbook info
' Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
Dim Wb1 As Workbook ' This will be set later when the workbook is opened
Dim MyPath As String: Let MyPath = "C:\Users\sk\Desktop" '  ".....The file will be located in C:\Users\sk\Desktop ....
Dim strWb1 As String: Let strWb1 = "sample.xlsx" '                                                          " ....and file name is sample.xlsx
'1b) Worksheets info
Dim Ws1 As Worksheet ' This will be set later when the workbook is opened)
Dim Lr1 As Long '      Let Lr1 = 10 for sample file  , but we will determine it dynamically after opening the file
Rem 2 Open file   "..... file is not opened so we have to open the file by vba
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\sample.xlsx"
 Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1  '  ...both files are located in same place
' Workbooks.Open Filename:=MyPath & "\" & strWb1              '  ...file will be located in C:\Users\sk\Desktop
 Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
 Set Ws1 = Wb1.Worksheets.Item(1)

' make Lr1 dynamic .... http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11474&viewfull=1#post11474
 Let Lr1 = Ws1.Range("C" & Ws1.Rows.Count).End(xlUp).Row
Rem 3 The Process ...using VBA arrays
'3_a) First we bring all the data into an array. (We also take in the column D values, even if the column D is empty)
Dim arrDta() As Variant
 Let arrDta() = Ws1.Range("A1:D" & Lr1 & "").Value
'3_b) Now we do the calculations looping through the row data held internally in the data array, arrDta()
Dim Cnt As Long
    For Cnt = 2 To Lr1
     Let arrDta(Cnt, 4) = arrDta(Cnt, 2) * (1.5 / 100) * 56 ' .. like.. column "D" = column "B" * (1.5/100) * 56
    Next Cnt
'3_c) Finally we paste out all the calculated values ( and also the original data )  in one go
 Ws1.Range("A1:D" & Lr1 & "").Value = arrDta()
Rem 4 save it and close it
 Wb1.Save
 Wb1.Close
End Sub

_e) Evaluate Range
see next post:

**DocAElstein** · 09-13-2019, 12:40 PM

_e) Evaluate Range

It is possible to get array type calculations in Excel. Nobody fully understands this topic, and a lot of things are found by chance to work in a way such as to do array type calculations, or rather , array type results can be obtained.

Evaluate Range techniques often allow a looping process to be replaced ba a single line of code. Broadly this arises due to two things:
_1) Excel frequently updates all cells in a spreadsheet by going across the columns in a row , then down a row, then across the columns in the next row … etc.
Usually a user "using" a single cell is like when it selected, and/ or the carriage Return key is used, and so it appears to us as if the cell is Updated and displayed at one time. There are various ways to display more than one cell in a single spreadsheet update.
_2) In VBA there is an Evaluate Method ( https://docs.microsoft.com/en-us/off...ation.evaluate ). In simplified terms, this allows calculation within VBA as if the calculations were written and done in a spreadsheet.

It is possible sometimes to get the Evaluate function to return an array representing the calculations across a range
There is no clear documentation on any of the array type things discussed in this post, and it is often suggested that getting array results in any form in Excel has occurred by chance and no one understands fully what is going on.

As an example, considering the last macro which looped to produce an array based on doing these calculations of this form, from down rows of 2 to Lr1
B2*(1.5/100)*56
B3*(1.5/100)*56
B4*(1.5/100)*56
_…. etc.

We find that Rem 3 from the last macro, Sub Vixer9d() , can be replaced by

Code:

Rem 3 The Process ...   using Evaluate Range
 Ws1.Range("D2:D" & Lr1 & "").Value = Ws1.Evaluate("=" & Range("B2:B" & Lr1 & "").Address & "*(1.5/100)*56")

The purpose of ("=" & Range("B2:B" & Lr1 & "") is to give us the formula form of like
=B2:B10
Hence the Range used does not need to be Qualified, such as by a worksheet, like in Ws1.Range
( There is an alternative form of Evaluate(" __ ") , which is often referred to as the "shorthand form" of Evaluate(" __ ") . It looks like this _ [ ___ ] _ . So you may now see what Mark L was suggesting here: https://www.excelforum.com/excel-pro...ml#post5190685 )

It is , however , important to qualify Evaluate. this is because we want to do an evaluation as if the formula within Evaluate(" ___ ") , was in the cell in worksheet, Ws1. If we omit the qualifying _ Ws1. _ , before the Evaluate , then we may do an evaluation of the formula in a different worksheet.

Code:

Sub Vixer9e() ' demo  for  Evaluate Range
Rem 1 Workbook and worksheets info
'1a) Workbook info
' Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
Dim Wb1 As Workbook ' This will be set later when the workbook is opened
Dim MyPath As String: Let MyPath = "C:\Users\sk\Desktop" '  ".....The file will be located in C:\Users\sk\Desktop ....
Dim strWb1 As String: Let strWb1 = "sample.xlsx" '                                                          " ....and file name is sample.xlsx
'1b) Worksheets info
Dim Ws1 As Worksheet ' This will be set later when the workbook is opened)
Dim Lr1 As Long '      Let Lr1 = 10 for sample file  , but we will determine it dynamically after opening the file
Rem 2 Open file   "..... file is not opened so we have to open the file by vba
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\sample.xlsx"
 Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1  '  ...both files are located in same place
' Workbooks.Open Filename:=MyPath & "\" & strWb1              '  ...file will be located in C:\Users\sk\Desktop
 Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
 Set Ws1 = Wb1.Worksheets.Item(1)

' make Lr1 dynamic .... http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11474&viewfull=1#post11474
 Let Lr1 = Ws1.Range("C" & Ws1.Rows.Count).End(xlUp).Row
Rem 3 The Process ...   using Evaluate Range
 Ws1.Range("D2:D" & Lr1 & "").Value = Ws1.Evaluate("=" & Range("B2:B" & Lr1 & "").Address & "*(1.5/100)*56")
Rem 4 save it and close it
 Wb1.Save
 Wb1.Close
End Sub

Using Evaluate often results in a much shorter coding.

For example, taking Sub Vixer9e() , and making a few other simplifications we can come up with a much shorter coding.

Code:

Sub Vixer9f() ' simplified coding ( using Range Evaluate )
 Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "sample.xlsx"  '  ...both files are located in same place
Rem 3 The Process ...   using Evaluate Range
 ActiveSheet.Range("D2:D" & ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row & "").Value = ActiveSheet.Evaluate("=" & Range("B2:B" & ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row & "").Address & "*(1.5/100)*56")
Rem 4 save it and close it
 ActiveWorkbook.Close savechanges:=True
End Sub

I personally do not like such coding because
_(i) They are more difficult to understand, especially at a later date,
_(ii) They are less flexible for adjustment.
_(iii) There may be some missing detail which might cause the coding to fail sometimes in certain circumstances

**DocAElstein** · 09-13-2019, 04:41 PM

in support of this post:
https://excel.tips.net/T001940_Hidin...ell_Value.html
https://excel.tips.net/T001940_Hiding_Rows_Based_on_a_Cell_Value.html

Hello Ryanne
Rather than modifying the coding, it would probably be easier to use a simple "events" type coding which automatically kicks in when a range value is changed in a worksheet. Something of this form:.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
 Target.EntireRow.Hidden = True
End Sub


































'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
'End Sub

This coding will need to be in a worksheet code module.
Follow , for example these 6 steps to create such a coding:

_1) Right click on the tab of the worksheet of interest
_2) Select View Code
Worksheet code module via View Code after right click on tab.JPG : : https://imgur.com/ZiOuRVT
Attachment 2426

_3) Select the left side drop down list
_4) Select Worksheet
Worksheet.JPG : https://imgur.com/tCwHKBo
Attachment 2427

_5) Select the right drop down list
_6) Select Change
Change.JPG : https://imgur.com/NkbNPsL
Attachment 2428

( Delete or ' comment out any other coding, such as the Private Sub Worksheet_SelectionChange which may have appeared automatically at step 4 )

You can now add your coding within the Private Sub Worksheet_Change

( Hit Alt+F11 to return to spreadsheet view )

The coding will kick off automatically when you change any cell value. It will hide the entire row which contains the cell whose value you changed

In the uploaded file , I have added the coding to the third worksheet code module

Alan

**DocAElstein** · 10-04-2019, 04:18 PM

Some supporting notes for some other stuff...

_3) Regarding the remaining leftover hide.me app on Windows 7 machine after I de- installed it
….( hide.me that was still showing in my program list after I had de installed, and note also that on restarting the windows 7 machine with the remaining left over hideme app, a hideme typically window came up , did something for a while, then another hide me window came up and offered me the chance to buy premium!! )
Strange remaining hide me thing after de install.JPG : https://imgur.com/4HsLmDN
Attachment 2439

I tried the Revo Unistaller as suggested, http://revo-uninstaller.en.softonic.com/ .
This was also not able to de install the hide.me that was still showing in my program list after I had de installed it: Revo also cannot de install the remaining hide.me.jpghttps://imgur.com/HY7pSIy
After this failed attempt, I performed the scan that Revo offered.
This scan took several hours. The scan first showed these left over registry things, Revo scan found left over hideme.jpg : https://imgur.com/r99WKN6
Attachment 2440
, which I chose to delete: Choose to delete found left over hideme.jpg , Deleting scan found left over hideme.jpg : https://imgur.com/aoToZJ2 , https://imgur.com/5jsACjQ .
Then the following left over files and folders were also shown, Revo scan found left over hideme Files and Folders.jpg : https://imgur.com/78YKmd4
Attachment 2441
, which I also chose to delete: Choose to delete Revo scan found left over hideme Files and Folders.jpg , Deletingf Revo scan found left over hideme Files and Folders.jpg : https://imgur.com/VAlBzED , https://imgur.com/2YoSSML .
Finally it appears that all left over hide.me files are gone: hideme no longer listed in programs.jpg : https://imgur.com/9aentWL
A restart is said to remove some files, .. Revo says remainig files will be deleted by restart.jpg : https://imgur.com/QTjDtf1 , so I restarted.
It seemed at this stage, ( after a restart) that the hideme app was completely removed.

**DocAElstein** · 10-29-2019, 02:00 PM

In support of answers to these Threads:
http://www.excelfox.com/forum/showth...ified-email-id
https://stackoverflow.com/questions/...75857_58525487
https://stackoverflow.com/questions/...-be-sent-to-sm
https://stackoverflow.com/questions/...-less-and-less

Register an account with Freemail German Telekom, for use with CDO.Message Send program

**DocAElstein** · 11-06-2019, 05:06 PM

In support to answer of this Thread:
http://www.excelfox.com/forum/showth...ll=1#post11557

_____ Workbook: VBA.xls ( Using Excel 2007 32 bit )

_Row\^Col	A
1	1234
2	5678
3	91011
4	12131415
5	1617181920

Worksheet: abc
'match column A of abc sheet with column A of def sheet
' if it matches then delete that data in column A of def
' "match column A of abc sheet with column A of def sheet if it matches then delete that data in column A of def"

_____ Workbook: VBA.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	C
1	1234
2	15678
3	191011
4	112131415
5	16171811920	after the process completed delete all the data in this sheet

Worksheet: def
_____ Workbook: VBA Exampled.xls ( Using Excel 2007 32 bit )

15678

191011

112131415

16171811920

Worksheet: def
_____ Workbook: VBA Exampled.xls ( Using Excel 2007 32 bit )

'and the data which are not matched compare that data ( here 'that data' means unmatched data in abc. Right? ) with Fake Data and
' if matched then delete and again if there will be unmatched data ( here also 'unmatched data' means unmatched data in abc. Right? ) then
_____ Workbook: VBA.xls ( Using Excel 2007 32 bit )

_Row\^Col	A
1	115678
2	191011
3	1112131415
4	1.16172E+11

Worksheet: Fake Data
_____ Workbook: VBA Exampled.xls ( Using Excel 2007 32 bit )

15678

112131415

16171811920

Worksheet: def

115678

1112131415

1.16172E+11

Worksheet: Fake Data

'................................................. ...................if there will be unmatched data then
' compare that data with complete data
_____ Workbook: VBA.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	115678
2

Worksheet: Completed
' if found then delete and again if there will be unmatched data

1112131415

1.16172E+11

Worksheet: Fake Data
then
' copy that data and paste it to missing data sheet in column A ( and finally delete all the data in def : after the process completed delete all the data in this sheet )

Originally Posted by fixer

the final result is in missing data sheet plz see
missing data sheet already has data and we have pasted the result below that data(the data starts with A2 is the result)
A1 in missing data sheet already has data so we putted the result below that plz see sir
....in simple words with def sheet the data which are present in column A doesnt match with any sheet column A data then put that data in missing data sheet sir (sheet can be many)

_____ Workbook: VBA.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	11111158
2	1112131415
3	1.16172E+11
4

Worksheet: Missing data
_____ Workbook: VBA Exampled.xls ( Using Excel 2007 32 bit )

11111158

1112131415

1.16172E+11

Worksheet: Missing data

**DocAElstein** · 11-06-2019, 06:43 PM

follow on from last post:

Before

Originally Posted by fixer

the final result is in missing data sheet plz see
missing data sheet already has data and we have pasted the result below that data(the data starts with A2 is the result)
A1 in missing data sheet already has data so we putted the result below that plz see sir

_____ Workbook: VBA Before.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	11111158
2
3
4

Worksheet: Missing data
_____ Workbook: VBA Before.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	1234
2	5678
3	91011
4	12131415
5	1617181920
6

Worksheet: abc
_____ Workbook: VBA Before.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	C
1	1234
2	15678
3	191011
4	112131415
5	16171811920	after the process completed delete all the data in this sheet

Worksheet: def
_____ Workbook: VBA Before.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	115678
2	191011
3	1112131415
4	1.16172E+11
5

Worksheet: Fake Data
_____ Workbook: VBA Before.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	115678
2

Worksheet: Completed
_____ Workbook: VBA Before.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	11111158
2
3
4

Worksheet: Missing data

**DocAElstein** · 11-06-2019, 07:56 PM

Follow on from last 2 posts

Explanation attempt
Column A of worksheet abc is compared with column A of worksheet def, looking for matches in data. If data in Column A of worksheet def is also found in column A of worksheet abc, then that matched data in column A of worksheet def is deleted
_____ Workbook: VBA worked excample.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	1234
2	5678
3	91011
4	12131415
5	1617181920
6

Worksheet: abc
_____ Workbook: VBA worked excample.xls ( Using Excel 2007 32 bit )

_Row\^Col	A
1	1234
2	15678
3	191011
4	112131415
5	16171811920

Worksheet: def
Column A of worksheet abc is compared with column A of worksheet def, looking for matches in data. If data in Column A of worksheet def is also found in column A of worksheet abc, then that matched data in column A of worksheet def is deleted
_____ Workbook: VBA worked excample.xls ( Using Excel 2007 32 bit )

1234

15678

191011

112131415

16171811920

Worksheet: def (modified)

The remain data in worksheet def is now compared with column A of worksheet Fake Data.
_____ Workbook: VBA worked excample.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	115678
2	191011
3	1112131415
4	1.16172E+11
5

Worksheet: Fake Data
If data in Column A of worksheet Fake Data is also found in column A of the modified worksheet def, then that matched data in column A of worksheet Fake Data is deleted
_____ Workbook: VBA worked excample.xls ( Using Excel 2007 32 bit )

115678

191011

1112131415

1.16172E+11

Worksheet: Fake Data (modified)

The remaining data in column A of modified worksheet Fake Data is now compared with column A of worksheet Completed
_____ Workbook: VBA worked excample.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	115678
2

Worksheet: Completed
If there is a match in data in columns A of worksheet Completed, and column A of worksheet Fake Data, then delete that matched data from worksheet Fake Data
_____ Workbook: VBA worked excample.xls ( Using Excel 2007 32 bit )

115678

191011

1112131415

1.16172E+11

Worksheet: Fake Data (modified)

If there is now any remaining data in column A of modified Fake Data, then that data is added to column A of missing data , ( in the given example, VBA Before.xls , A1 in missing data sheet already had data
_____ Workbook: VBA worked excample.xls ( Using Excel 2007 32 bit )

_Row\^Col	A
1	11111158
2

Worksheet: Missing data
so we put the result below that:
_____ Workbook: VBA worked excample.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	11111158
2	1112131415
3	1.16172E+11
4

Worksheet: Missing data (final)

**DocAElstein** · 12-18-2019, 11:04 PM

Coding for this Thread
http://www.excelfox.com/forum/showth...ll=1#post11794

Code:

Sub MakeFormulas3() '  http://www.excelfox.com/forum/showthread.php/2390-Apply-Vlookup-formula-in-all-the-available-sheets-in-a-workbook?p=11794&viewfull=1#post11794
Rem 1 '  Workbooks info
' 1a This months book, this workbook. It is the outout book for the current month
Dim ThisMonthsLatestBook As Workbook, LisWbName As String
 Set ThisMonthsLatestBook = ThisWorkbook ' ActiveWorkbook
 Let LisWbName = ThisMonthsLatestBook.Name
    If InStr(7, LisWbName, Format(Now(), "MMM"), vbTextCompare) = 0 Then MsgBox Prompt:="This workbook is not for " & Format(Now(), "MMMM"): Exit Sub
Dim BookN As Long
 Let BookN = Mid(LisWbName, 5, InStr(5, LisWbName, "_", vbBinaryCompare) - 5)
' 1b Last months book
Dim sourceBookName As String
 Let sourceBookName = "Book" & BookN - 1 & "_" & Format(DateAdd("m", -1, Now()), "MMM YYYY") & ".xlsm"
Dim sourceBook As Workbook
 Set sourceBook = Workbooks.Open(ThisWorkbook.Path & "\" & sourceBookName)
Rem 2  Make records worksheet                                                                  Sub MakeWorkSheetIfNotThere()
'Dim Wb As Workbook '                                   ' ' Dim:  ' Preparing a "Pointer" to an Initial "Blue Print" in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Objec of this type ) . This also us to get easily at the Methods and Properties throught the applying of a period ( .Dot) ( intellisense )                     '
' Set Wb = ActiveWorkbook '  '                            Set now (to Active Workbook - one being "looked at"), so that we carefull allways referrence this so as not to go astray through Excel Guessing inplicitly not the one we want...         Set: Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191                                '
     If Not Evaluate("=ISREF(" & "'" & "Records" & "'!Z78)") Then '   ( the '  are not important here, but iin general allow for a space in the worksheet name like  "My Records"
     ThisMonthsLatestBook.Worksheets.Add After:=ThisMonthsLatestBook.Worksheets.Item(Worksheets.Count) 'A sheeet is added and will be Active
    Dim wsRcds As Worksheet '
     Set wsRcds = ThisMonthsLatestBook.Worksheets.Item(ThisMonthsLatestBook.Worksheets.Count)        'Rather than rely on always going to the active sheet, we referr to it Explicitly so that we carefull allways referrence this so as not to go astray through Excel Guessing implicitly not the one we want...    Set: Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191            ' Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191
     wsRcds.Activate: wsRcds.Cells(1, 1).Activate ' ws.Activate and activating a cell sometimes seemed to overcome a strange error
     Let wsRcds.Name = "Records"
    Else ' The worksheet is already there , so I just need to set my variable to point to it
     Set wsRcds = ThisWorkbook.Worksheets("Records")
    End If
'                                                                                               End Sub
Rem 3 looping through worksheets
Dim C As Long, I As Long
'C = ActiveWorkbook.Worksheets.Count
 'Let C = ThisWorkbook.Worksheets.Count
 Let C = ThisMonthsLatestBook.Worksheets.Count - 1  '   -1 since last worksheet is records worksheet
    'For I = 1 To C
'Application.ScreenUpdating = True
    For I = 1 To C   '   Sheet1  , Sheet2   , Sheet3 .......
    'what are  our worksheets?                         I   =  1        ,       2 ,      3    ..........
    Dim sourceSheet As Worksheet
     Set sourceSheet = sourceBook.Worksheets.Item(I) '     ("Sheet1")  , Sheet2   , Sheet3 ........
    Dim outputSheet As Worksheet
     Set outputSheet = ThisWorkbook.Worksheets.Item(I) ' ("Sheet1")    , Sheet2   , Sheet3 ........
       
        'Determine last row of source
        With sourceSheet
        Dim SourceLastRow As Long
         SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
        With outputSheet
        'Determine last row in col P
        Dim OutputLastRow As Long
         OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
        End With
        'Apply our formula in records worksheet
        With Worksheets("Records")
         Let .Cells.Item(1, I).Value = sourceSheet.Name   '  Header in column as worksheet name
         '.Range("Q2:Q" & OutputLastRow).Formula = "=VLOOKUP($A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$P$" & SourceLastRow & ",3,0)"
         .Range("" & CL(I) & "2:" & CL(I) & "" & OutputLastRow).Value = "=VLOOKUP(" & outputSheet.Name & "!$A2,'" & sourceBook.Path & "\" & "[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$P$" & SourceLastRow & ",3,0)"
'        .Range("" & CL(I) & "2:" & CL(I) & "" & OutputLastRow).Value = .Range("" & CL(I) & "2:" & CL(I) & "" & OutputLastRow).Value
        End With
     'MsgBox ActiveWorkbook.Worksheets(I).Name
     MsgBox ActiveWorkbook.Worksheets.Item(I).Name
    Next I
'Next P
Rem 4
Dim cel As Range
    With Worksheets("Records").UsedRange
        For Each cel In .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
            If IsError(cel.Value) Then
            '
            Else
                If cel.Value < 3 Then
                 cel.Font.Color = vbRed
                Else
                 cel.Font.Color = vbGreen
                End If
            End If
        Next cel
    End With
    
'Close the source workbook, don't save any changes
 sourceBook.Close False
' Application.ScreenUpdating = True
End Sub
'   https://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Function CL(ByVal lclm As Long) As String 'Using chr function and Do while loop      For example http://www.excelforum.com/excel-programming-vba-macros/796472-how-to-go-from-column-number-to-column-letter.html
Dim rest As Long 'Variable for what is "left over" after subtracting as many full 26's as possible
    Do
    '    Let rest = ((lclm - 1) Mod 26) 'Gives 0 to 25 for Column Number "Left over" 1 to 26. Better than ( lclm Mod 26 ) which gives 1 to 25 for clm 1 to 25 then 0 for 26
    '    Let FukOutChrWithDoWhile = Chr(65 + rest) & FukOutChrWithDoWhile 'Convert rest to Chr Number, initially with full number so the "units" (0-25), then number of 26's left over (if the number was so big to give any amount of 26's in it, then number of 26's in the 26's left over (if the number was so big to give any amount of 26 x 26's in it, Enit ?
    '    'OR
    Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL
    Let lclm = (lclm - (1)) \ 26 'This gives the number of 26's ( if any ), but just the excact part, in the next number down , - so applying the rest formula to this new number will again leave a difference "left over" rest.
    'lclm = (lclm - (rest + 1)) \ 26 ' As the number is effectively truncated here, any number from 1 to (rest +1)  will do in the formula
    Loop While lclm > 0 'Only loop further if number was big enough to still have 0-25's in it
End Function

**DocAElstein** · 12-19-2019, 06:28 PM

Notes in support of this Forum Question:
https://www.eileenslounge.com/viewto...p?f=18&t=33834

Trying to figure out how Excel Clipboard ( or maybe Office clipboard ) is interacting with the Windows clipboard when trying to paste into Excel a text string,….

I have a text string. I add to it, manipulate it a bit, then put it in "the clipboard", ( probably the windows clipboard ) , then do an Excel Worksheets Paste …

Simplified example…
I have this already in a string,
"A" & vbCr & vbLf & "C"
Seen in another way:
A_vbCr_vbLf_B
The first representation is in a typical code line convention, the second an attempt at a more "real" view
There are 4 characters there. The middle two are examples of types that are often referred to as "invisible" characters. (These two typically instruct systems to go to a new line)
Most software that visibly gives you some sort of text to see , would usually interpret that as two lines of text. For example, Excel would usually interpret that such as to display you something like this, if you somehow "put it in" the start ( top left ) of a spreadsheet:

_Row\^Col	A
1	A
2	C

I want to put an extra cell with split line text in it, simplified like this:

_Row\^Col	A
1	A
2	X Y
3	C

Sometime or other I have learnt that Excel recognises a single vbLf to split up lines of text within a cell. But it turns out to be bit more complicated than that if you want the text string in the Windows Clipboard to come out as you want it in Excel.

The second part of this macro seems to usually achieve the second screenshot above

Code:

Sub TestvbLf_1()
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Dim StringBack As String
' Fill two rows , in 1 column
 ActiveSheet.Cells.Clear ' This is important to remove any formatting that might distort results
 objDataObject.SetText "A" & vbCr & vbLf & "C": objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("A1")
 objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
 Call WtchaGot(StringBack) '         "A" & vbCr & vbLf & "C"
' Put an extra cell with split line text in it
 ActiveSheet.Cells.Clear '
 objDataObject.SetText "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C": objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("A1")
 objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
 Call WtchaGot(StringBack) '          "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C"
 
End Sub

( The function, WtchaGot( ) can be found here: http://www.excelfox.com/forum/showth...ll=1#post10946 )

Here are some further sample code snippets and discussions

This code snippet suggest to me that the Windows clipboard is being used, as .Clear does not empty "the clipboard" , as one more typically annoyingly experiences in Excel work when copying things manually or with VBA in Excel

Code:

Sub TestvbLf_2()
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Dim StringBack As String

' Put an extra cell with split line text in it
 ActiveSheet.Cells.Clear '
 objDataObject.SetText "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C": objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("A1")
 objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
 Call WtchaGot(StringBack) '   ---    "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C"
 
 ActiveSheet.Cells.Clear ' This does not clear the clipboard, so next line gives the same results, which...
 ActiveSheet.Paste Destination:=ActiveSheet.Range("A1") '   suggests that the Windows  clipboard is being used
 Call WtchaGot(StringBack) '   ---    "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C"
'
End Sub

Finally we see this, the orange of the clipboard icon indicating something is in "the clipboard", presumably the windows clipboard, since , as said, .Clear does not clear that icon, and also we see that the office clipboard is empty…
TestvbLf_2.JPG : https://imgur.com/dEbsaPE
Attachment 2565

( Using Excel 2007 32 bit )

A	19 Dez 2019 Lenf is 11	A "X Y" C
X Y	1 A	65
C	2	13
	3	10
	4 "	34
	5 X	88
	6	10
	7 Y	89
	8 "	34
	9	13
	10	10
	11 C	67

Worksheet: WotchaGotInString
The second two columns are produced by function WtchaGot( ) , and give a breakdown of the 11 characters in the string:
"A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C"
A vbCr vbLf " X vbLf Y " vbCr vbLf C

Further investigation in next post