VBA Code help

V

vbeall

New Member
Joined
Apr 28, 2015
Messages
2
I cannot seem to find information about what i need a VBA code to do.
If anyone can help me, that would be greatly appreciated.

I have this spreadsheet to log the customers that recieve service, date of service, amount they owe, and if i recieved payment or not.

(Income sheet)
[TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl69, width: 72, bgcolor: transparent, align: right"]Date
[/TD]
[TD="class: xl67, width: 205, bgcolor: transparent"]Customer
[/TD]
[TD="class: xl67, width: 128, bgcolor: transparent"]Service
[/TD]
[TD="class: xl68, width: 101, bgcolor: #FFFFCC"]Amount
[/TD]
[TD="class: xl66, width: 110"]Payment
[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 72, bgcolor: transparent, align: right"]22-Apr
[/TD]
[TD="class: xl67, width: 205, bgcolor: transparent"]Blah Blah
[/TD]
[TD="class: xl67, width: 128, bgcolor: transparent"]Mow
[/TD]
[TD="class: xl68, width: 101, bgcolor: #FFFFCC"] $ 40.00
[/TD]
[TD="class: xl66, width: 110"]Cash
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: right"]22-Apr
[/TD]
[TD="class: xl67, bgcolor: transparent"]Blah Blah
[/TD]
[TD="class: xl67, bgcolor: transparent"]Mow
[/TD]
[TD="class: xl68, bgcolor: #FFFFCC"] $ 40.00
[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: right"]22-Apr
[/TD]
[TD="class: xl67, bgcolor: transparent"]Blah Blah
[/TD]
[TD="class: xl67, bgcolor: transparent"]Mow
[/TD]
[TD="class: xl68, bgcolor: #FFFFCC"] $ 45.00
[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: right"]22-Apr
[/TD]
[TD="class: xl67, bgcolor: transparent"]Blah Blah
[/TD]
[TD="class: xl67, bgcolor: transparent"]Mow
[/TD]
[TD="class: xl68, bgcolor: #FFFFCC"] $ 40.00
[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: right"]23-Apr
[/TD]
[TD="class: xl67, bgcolor: transparent"]Blah Blah
[/TD]
[TD="class: xl67, bgcolor: transparent"]Mow
[/TD]
[TD="class: xl68, bgcolor: #FFFFCC"] $ 85.00
[/TD]
[TD="class: xl66, bgcolor: transparent"]Partners
[/TD]
[/TR]
</tbody>[/TABLE]

I need to create a formula or code that will automatically insert this information in the invoice sheet when the customers name is inserted in the Bill To section.

(Invoice Sheet)
[TABLE="width: 504"]
<tbody>[TR]
[TD="class: xl67, width: 72, bgcolor: transparent"]Bill To:
[/TD]
[TD="class: xl68, width: 205, bgcolor: transparent"]Blah Blah
[/TD]
[TD="class: xl66, width: 198, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 90, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]55555 Red Oak Dr
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Egypt
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]DESCRIPTION
[/TD]
[TD="class: xl71, bgcolor: transparent"]DATE
[/TD]
[TD="class: xl71, bgcolor: transparent"]QUANTITY
[/TD]
[TD="class: xl71, bgcolor: transparent"]AMOUNT
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent, colspan: 2"]landscape
[/TD]
[TD="class: xl75, bgcolor: transparent"]20-Apr
[/TD]
[TD="class: xl76, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"] $ 500.00
[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"]TOTAL
[/TD]
[TD="class: xl77, bgcolor: transparent"] $ 500.00
[/TD]
[/TR]
</tbody>[/TABLE]



Essentially:
When the customers name is entered, I need it to take the invoice date, which will be the day that the invoice is being prepared, lets say (May 1, 2015), and look for all information from that one customer in the Income sheet from May 1 back 31 days (so the month before the invoice date).
I need it then to return:
1. the date of service in the date column of the invoice sheet
2. the type of service in the description column of the invoice sheet
3. Quantity is always one.
4. the amount from the service in the amount column of the invoice sheet.

I do not want it to return any information that would be green in the payment column of the income sheet being that that means that service has already been paid for.

Is this possible?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
You must log in or register to reply here.

Similar threads

S
  • Question Question
Extract Rows of Data Using VLOOKUP With Multiple Criteria and Import to an Invoice for Each Customer
2 3 4
Replies
36
Views
2K
mumps
mumps
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
377
Akuini
Akuini
A
  • Question Question
Create report by matching part of item and populate for each cell under item across sheets
Replies
2
Views
203
Ali M
A
E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
G
  • Solved
Need Help with VBA Code, Not sure if its possible.
2
Replies
15
Views
1K
Akuini
Akuini

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

Share this page

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