#1
|
|||
|
|||
Help with creating a script with VBA
Hi guys, I need your help. I am supposed to automate a process for work, but unfortunately with a recorded macro it does not work. Therefore, I turn to you, the community, in the hope that you can help me (with VBA). I need to compare two files and if there is a match, a certain value should be spit out from a certain cell. I have attached the two files. One file (Schweben.xslx) changes every day (so the number of lines), so the VBA code should be able to automatically detect the length of the file and process all lines to the end. The file (Rest.xlsx) changes once a month. The data in the files are changed and in reality much longer. I have also changed the file names, so it would be very nice if you could explain to me at which point of the code I then have to change the respective file name/file path. The code should be able to do the following.
1) The source file is the Schwebe.xlsx. First, the column H (Nominal (open)) in table 1 is to be copied and pasted into column A in the newly created table 2. The column E (ISIN) is to be copied into column B of table 2. (Without headings) 2) Then, column K (Nominal) of Table 1 (Rest.xlsx) is to be copied into column F of Table 2 of Schweben.xlsx. (Without heading) 3) The same for column H of the Rest.xlsx. This is to be copied into column G of table 2 of Schweben.xlsx. (Without heading) This column is to be adapted now something. It should be the "Command" --> Data --> Text in columns --> Fixed width --> then move to the end of the ISIN values --> and Finish. 4) Now the column D of the Rest.xlsx will be copied into the column J of the table 2 of the Schwebe.xlsx. 5) Now the comparison happens. The basic command looks something like this: =IFERROR(INDEX(J1:J26;MATCH(A1:A19&B1:B19;F1:F26&G 1:G26;0)); "No match") This command, as mentioned above, should automatically adjust to the length of the columns and always match everything. This command should be executed in column C. 6) As a final step, all values in column C that are not equal to the IFERROR value-if-error should be automatically copied and placed in a new created file that the user can then manually name and save. Any help will be appreciated. Thanks in advance |
#2
|
|||
|
|||
Thats what i got for bullet point 1:
Code:
Sub CopyRowE() Dim LastRowE As Long Dim LastRowH As Long Dim LastDataRow As Long Dim CopyData As Long With Tabelle1 LastRowE = .Range("E9999").End(xlUp).Row LastRowH = .Range("H9999").End(xlUp).Row .Range("E2:E" & LastRowE).Copy .Range("CA1").PasteSpecial .Range("H2:H" & LastRowH).Copy .Range("CB1").PasteSpecial LastDataRow = .Range("CB999999").End(xlUp).Row .Range("CA1:CB" & LastDataRow).Copy Sheets.Add ActiveSheet.Range("A1").PasteSpecial .Range("CA1:CB" & LastDataRow).ClearContents Tabelle1.Select .Range("A1").Select End With End Sub Any idea for the other bullet points? How can i do the same for a different file? |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Script Directory | charlesdh | Excel Programming | 3 | 06-12-2018 01:36 PM |
Run a script disappeared, now what? | ProgramSam | Outlook | 2 | 02-19-2018 05:00 AM |
Outlook VBA Script | kcm5153 | Outlook | 1 | 04-07-2015 11:41 PM |
Bolding in script | ksigcajun | Word VBA | 10 | 02-23-2015 08:29 AM |
Help with VBA script | nsyrax | Word VBA | 1 | 01-18-2014 03:38 AM |