Solved

Adjust numbers on spreadsheet using macro

Posted on 2013-01-25
8
310 Views
Last Modified: 2013-01-25
Hi

I have a spreadsheet which has qtys of printer toners used in H:H

When I have a toner delivery I want to enter toners delivered in N:N

I then want to be able to push a button on the spreadsheet and the macro will deduct N:N from H:H and then clear the cells in N:N ready for the next toner delivery.

How do I do this

Office 2010
0
Comment
Question by:GRiTech
  • 5
  • 3
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38818524
Setup a button and insert this code

    Columns("N:N").Copy
    Columns("H:H").PasteSpecial Operation:=xlSubtract
0
 
LVL 1

Author Comment

by:GRiTech
ID: 38818549
Thanks for quick reply do I insert it just like that

 Columns("N:N").Copy
    Columns("H:H").PasteSpecial Operation:=xlSubtract

or is there other bits of code needed

Cheers
G
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38818564
Just that. Nothing else....if you know how to set up a button and code to go with it.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 1

Author Comment

by:GRiTech
ID: 38818651
OK that seems to work but it clears the fill colour assigned to column H:H also it doesnt clear the cells in N:N at the end

Cells in N:N are left selected
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 38818684
Sorry did not read the question closely enough.

    Columns("N:N").Copy
    Columns("H:H").PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract
    Columns("N:N").ClearContents
0
 
LVL 1

Author Comment

by:GRiTech
ID: 38818879
Cool thats brill nearly there, I only want to clear cells N5:N46
0
 
LVL 1

Author Comment

by:GRiTech
ID: 38819093
Its OK sorted it using

Range ("N3:N46").ClearContents

Thank you for your help
0
 
LVL 1

Author Closing Comment

by:GRiTech
ID: 38819096
Excellent advise
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question