Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-19-2022, 03:46 AM
lenziwag93 lenziwag93 is offline Help with creating a script with VBA Windows 11 Help with creating a script with VBA Office 2019
Novice
Help with creating a script with VBA
 
Join Date: Apr 2022
Posts: 2
lenziwag93 is on a distinguished road
Default 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
Attached Files
File Type: xlsx Rest.xlsx (9.2 KB, 5 views)
File Type: xlsx Schweben.xlsx (9.1 KB, 5 views)
Reply With Quote
  #2  
Old 04-20-2022, 12:57 AM
lenziwag93 lenziwag93 is offline Help with creating a script with VBA Windows 11 Help with creating a script with VBA Office 2019
Novice
Help with creating a script with VBA
 
Join Date: Apr 2022
Posts: 2
lenziwag93 is on a distinguished road
Default

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?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with creating a script with VBA 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
Help with creating a script with VBA 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:30 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft