VBA Code for add formulas and vlookup into workbook

CuteLeo

New Member
Joined
May 17, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Good morning. How are you all? Hope you all are doing great.

I'm looking for some urgent assistance in VBA codes. I have to make one VBA programme .xlsm. I've two files. One is my main file and other one is my lookup file.
I want that when I run program, it ask me about my required file and I tell about file (b/c file changes every month). After it receives given file it will apply 2 formulas, gross amount and net amount (after finding their required columns for formula) and day part lookup from lookup file. All these columns will be added at the end of the last available empty column. Below are the formulas:

gross amount = (unit rate/60) * duration
net amount = (gross amount/duration) * 30
Day part lookup from lookup file
Note: Column position isn't fixed and column heading names are fixed.

I've tried by myself to write a code, few lines are there and need assistance for a complete code according with my requirement. Below are few lines and 2 files are also attached.

VBA Code:
Private Sub Daypartandgrossandnetamount()

    'my Variables
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastrow  As Long
    Dim lastcolumn  As Long
   
    'Asking for opening required file
    If ws1 Is Nothing Then
        ChDrive ThisWorkbook.Path: ChDir ThisWorkbook.Path
        fn = Application.GetOpenFilename("ExcelBooks,*.xlsx", , "Select [Required File.xlsx]")
        If fn = "False" Then Exit Sub
        Set ws1 = Workbooks.Open(fn, False).Sheets("Sheet1")
    End If

    With ws1
   
    'Defined my last row & column
        lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
       
    End With
       

End Sub


I hope you understand what I mean. My english isn't good. If you have any problem then you may ask me about that. Thanks in advance for helping me.

Raw file.xlsx
K
11
Sheet1


Lookups.xlsx
I
11
Lookups
 
Last edited by a moderator:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

CuteLeo

New Member
Joined
May 17, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Sorry my files weren't attached properly. Below are proper attached files.

Raw File:
Raw file.xlsx
ABCDEFGHIJ
1TimeDurationSlot_PositionSlot_CountSlot_Position2Slot_Count2Slot_Position_AllSlot_Count_AllAd_SlotUnit Rate
203:30:4510339340341Third41
303:37:4510313932403241Other41
403:39:2310383939403941Second last41
504:03:5610212212217Second17
604:23:04991710181220Other20
701:03:31500001717017
801:32:4180000523023
905:25:0570000633033
1005:30:09500003233033
1106:46:40800001736036
1207:17:55800001229029
1309:05:14500001717017
1414:36:36600002124024
1517:07:14700001717017
1608:11:35200002707
1708:14:00100000318018
1808:14:43100000518018
1908:16:231100001018018
2008:17:341100001318018
2108:35:4130175818601860Other60
2208:45:0430415842604260Other60
2309:34:4830131414151415Second last15
2409:45:2530131613171317Other17
2510:37:0130710811811Other11
2610:47:213079811811Third last11
2711:08:49300008808
2811:47:34100000618018
2911:47:51110000718018
3011:49:521100001518018
3111:50:081100001618018
3211:58:33103558510Third10
3312:58:1010710913916Other16
3413:09:39200002707
3513:58:1510121512161219Other19
3614:19:20100000314014
3714:20:541100001014014
3814:21:161000001214014
3914:21:371100001414014
4014:58:251191211151118Other18
4115:58:031081110141017Other17
4216:31:49110000523023
4316:32:01100000623023
4416:33:381000001423023
4516:34:511000002123023
4616:58:2010262927312936Other36
4717:58:1710121513171320Other20
4819:58:3610111411151118Other18
4920:58:4510262827302936Third last36
5021:30:0120000210010
5100:27:401037311313Third13
5201:29:151058514716Other16
5301:30:1910889141116last16
5403:26:2510142415291532Other32
5503:40:3310323325329Third29
5604:24:1410222229235Second35
5704:26:14108229291035Other35
5804:31:0210202225292835Third last35
5904:48:1210101311171122Other22
6008:37:22600002202
6108:38:1910222222Second2
6209:30:40700002222022
6309:31:3310222222Second2
6410:32:17700001919019
6510:33:2810272828Second8
6611:39:29500002202
6711:40:391128210210Second10
6812:32:27600001414014
6912:33:3918252627Second7
7013:31:45600002121021
7113:32:5018272828Second8
7214:31:10600001010010
7314:32:2118232323Second3
7416:31:35600002222022
7516:32:4516211212212Second12
7617:31:10700004040040
7717:32:2514262729Second9
7818:51:3510101412181224Other24
7900:40:0110555555last5
8012:30:5210777777last7
8116:56:0918412414517Other17
8217:17:5517514514514Other14
8320:06:4910364747Third7
8420:22:1920777777last7
8520:40:539272727Second7
8620:57:559282829Second9
8703:38:4210313632373439Other39
8803:39:1710343635373739Third last39
8904:02:0310311311311Third11
9004:03:1410811811811Other11
9120:21:0610194119422045Other45
9220:27:2360041424445045
9320:38:1410203720382140Other40
9420:44:4260037383940040
9521:16:0310143714381541Other41
9621:20:4660027383041041
9721:51:3160012131314014
9822:25:4510132513261429Other29
9922:27:3260020262229029
10022:45:1210610610711Other11
Sheet1



Lookup File:
Lookups.xlsx
BC
4Daypart
5HourDaypart
60Late Night
71Late Night
82Late Night
93Late Night
104Late Night
115Late Night
126Morning
137Morning
148Morning
159Morning
1610Morning
1711Morning
1812Afternoon
1913Afternoon
2014Afternoon
2115Afternoon
2216Afternoon
2317Afternoon
2418Evening
2519Evening
2620Evening
2721Night
2822Night
2923Night
Lookups
 

CuteLeo

New Member
Joined
May 17, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi, Good day,
No one Is there to help me out kindly? If you don't understand my problem then I will explain. I request please consider answering my query.
 

Forum statistics

Threads
1,136,800
Messages
5,677,809
Members
419,721
Latest member
StuckInWork

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top