1 Export /Import VBA Macro-Cannot Do

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,155
Office Version
  1. 2019
Platform
  1. Windows
Hello all.
I've been working hard on a spreadsheet,, and need just 1 macro to finish it off, but I know
I can't write it myself,,, So I really could do with some help if possible.

I'll try and provide as much info as possible and a test sheet.

OK,,, here goes :-)

I have a worksheet called "Data Import". This needs to export some data to another sheet called "Daily Targets" The daily targets sheet cells that will be needed to import this data are Cells J23 (For a starting £ amount) & Cells; Range J24:J274, as daily amounts.
(AS A NOTE: there are 240days of data to be imported In the Range,, every 20th cell, is blank,, due to how I formatted the sheet,,, :-),,
So No data for cells J44,J65,J86,J107,J128,J149,J170,J191,J212,J233,J254

In the Daily import sheet, there are really 3 main steps.
Step 1. Cell F27 is a starting £ amount,, for this example £10,000,,
this needs to be exported to the "Daily Targets" sheet, & inserted into cell J23.

Now Step 2
=========
There are 4 parts to Step 2,, 2a,2b,2c, & 2d.
ONLY 1 of these needs to be filled in.
If more than 1 is filled in the macro needs a fail safe to say, "hey, you've made a mistake,,
only enter 1 part; a,or b or c or d for this step 2!"

Step 2a
======
Is Cell K15. This is a % cell, and someone would enter a daily % increase,, for example 0.5% This means,, everyday,, the Starting amount (Now in Cell J23,, IE £10,000 would increase by .05% every day,, compounding for 240 days
(I'm not sure how to write this 1 :-(
Example, if Cell K15 was 0.5%,, then the 1st cell in the "Daily Targets" sheet,
cell J24 would need to show £10,050

Step 2b
======
Is Cell K20. This is a Fixed £ gain amount. Example, If someone entered £100, this would mean that everyday, you would look for £100 gain.
So,, I think this formula is really no more than,J24:J274 must equal K20
(But the 11 other J cells would be blank as mentioned earlier),,,
Example,, If K20 was £100,, The 1st cell in the "daily Targets" sheet , cell J24 would now show £10,100, the 2nd cell J25 would be £10,200

Step 2c
======
Is Cell K25. This is a desired Year End amount. So if someone entered £50,000,, the formula would probably be no more than K25/ 240,, so all the J Cells have a even distribution of the £50,000.
Example; with a £50,000 amount inserted into K25, this gives a even £208.33 daily gain,, so Cell J24 (Day 1 in the Daily Target" sheet) would now show £10,208.33, & Day 2, Cell J25 would show £10,416.66 etc etc

Step 2d
======
Is Cell Range K36:K275,, this is a custom import,, IE you can manually enter 240 days of data.
So, if on Day 1 (Cell K36 of sheet Data Import) you wanted to gain £100, you would enter £100. This would mean the amount exported to Cell J24 in the daily targets sheet would now show £10,100 If Day 2 Cell K37 was £300,, Cell J25 of the "Daily Target" sheet would show a amount of £10,400
(All 240 cells would need data in for this to import,, so again, maybe a fail safe if there wasn't 240 entries

I hope all the above makes sense.
It's just a way for someone to import a starting equity £ (IE £10,000)
& ,,,,, several ways to insert Daily Targeted gains into the Range J24:J274.

I hope someone can help me with this please.
here is a link for a spreadsheet with it all configured.
http://dl.dropbox.com/u/16052166/MrExcel-Target-Sheet.zip
It's the last piece of the puzzle,, but my VBA isn't up to this :-(
It is a bit complicated, hence why I'm uploading a formatted sheet also
Many thanks for all your time with this
Best regards
John Caines
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Upvote 0

Forum statistics

Threads
1,224,550
Messages
6,179,463
Members
452,915
Latest member
hannnahheileen

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
Back
Top