[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Calculate the count and keep the results to a excel sheet

Posted on 2007-07-23
14
Medium Priority
?
400 Views
Last Modified: 2010-03-05
Hi,
Calculate the count and keep the results to a sheet
ex:
I want a way to maintain all the cd's i have
Want 2 boxes one should have inward and another outward
Inward means stock
Outward means given to some one.

If i put 20 cd's as inward.This record should be in sheet 2.Where as in sheet 1 i should have these options to enter inward or outward.

If given to some one.need to enter username,no of cd's etc.

Regards
Sharath
0
Comment
Question by:bsharath
  • 7
  • 6
14 Comments
 
LVL 5

Expert Comment

by:usarian
ID: 19546904
What's your starting point, do you already have your CDs typed into a list in Excel?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 19547689
bsharath,

Here's a macro to do the work:

Sub dataupdate()
Dim rng As Range
Dim celle As Range
Dim i As Long
Dim lastrow As Long
Dim lastrow2 As Long


Set rng = Range(Sheets("Inward - stock").[A1], Sheets("Inward - stock").[A65536].End(xlUp))

For Each celle In rng

Next celle

lastrow = Sheets("Inward - stock").[A65536].End(xlUp).Row

For i = lastrow To 2 Step -1
    If Sheets("Inward - stock").Cells(i, 1).Offset(0, 1) <> "" And Sheets("Inward - stock").Cells(i, 1).Offset(0, 2) <> "" Then
        Range(Sheets("Inward - stock").Cells(i, 1), Sheets("Inward - stock").Cells(i, 3)).Copy _
            Range(Sheets("Outward - lent").[A65536].End(xlUp).Offset(1, 0), Sheets("Outward - lent").[A65536].End(xlUp).Offset(1, 2))
        Sheets("Inward - stock").Rows(i).EntireRow.Delete
    End If
Next i

lastrow = Sheets("Outward - lent").[A65536].End(xlUp).Row

For i = lastrow To 2 Step -1
    If Sheets("Outward - lent").Cells(i, 4) <> "" Then
        Sheets("Outward - lent").Cells(i, 1).Copy Sheets("Inward - stock").[A65536].End(xlUp).Offset(1, 0)
        Sheets("Outward - lent").Rows(i).EntireRow.Delete
    End If
Next i

End Sub

The data layout on both Sheets is:

CD Title      Lent to        Date lent      Date returned

and the sheets are named:

Inward - stock
Outward - lent

I will post a link ot a file for you as soon as I am able (when it's working)

Patrick
0
 
LVL 11

Author Comment

by:bsharath
ID: 19547734
Ok
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Author Comment

by:bsharath
ID: 19547752
I get this.

Sub dataupdate()
Dim rng As Range
Dim celle As Range
Dim i As Long
Dim lastrow As Long
Dim lastrow2 As Long


Set rng = Range(Sheets("Inward - stock").[A1], Sheets("Inward - stock").[A65536].End(xlUp))

For Each celle In rng

Next celle

lastrow = Sheets("Inward - stock").[A65536].End(xlUp).Row

For i = lastrow To 2 Step -1
    If Sheets("Inward - stock").Cells(i, 1).Offset(0, 1) <> "" And Sheets("Inward - stock").Cells(i, 1).Offset(0, 2) <> "" Then
        Range(Sheets("Inward - stock").Cells(i, 1), Sheets("Inward - stock").Cells(i, 3)).Copy _
            Range(Sheets("Outward - lent").[A65536].End(xlUp).Offset(1, 0), Sheets("Outward - lent").[A65536].End(xlUp).Offset(1, 2))
        Sheets("Inward - stock").Rows(i).EntireRow.Delete
    End If
Next i

lastrow = Sheets("Outward - lent").[A65536].End(xlUp).Row

For i = lastrow To 2 Step -1
    If Sheets("Outward - lent").Cells(i, 4) <> "" Then
        Sheets("Outward - lent").Cells(i, 1).Copy Sheets("Inward - stock").[A65536].End(xlUp).Offset(1, 0)
        Sheets("Outward - lent").Rows(i).EntireRow.Delete
    End If
Next i

End Sub

The data layout on both Sheets is:

CD Title      Lent to        Date lent      Date returned

and the sheets are named:

Inward - stock
Outward - lent
0
 
LVL 45

Expert Comment

by:patrickab
ID: 19547826
To install a sub in a module:

1) Press ALT+F11
2) Find the workbook name, right-click on it
3) Select Insert/Module
4) Double click on the new Module
5) Paste the code into the Module under Option Explicit
6) ALT + F11 to return to the worksheet
7) ALT + F8 to select and run the macro

To install a sub in the code pane for a worksheet:
1) Right-click the worksheet tab
2) Select View Code
3) Paste the code in the module sheet
4) ALT + F11 to return to the worksheet
5) ALT + F8 to select and run the macro

If the above procedure doesn't work, then you need to change your macro security setting with - Tools/Macro/Security select Medium, press OK.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 19547896
Here's a link to a file for you:

https://filedb.experts-exchange.com/incoming/ee-stuff/4113-bsharath_02.zip

The items will only be returned to the first sheet when you have put a date in the 'Date returned' column on the 'Outward - lent' sheet.
0
 
LVL 11

Author Comment

by:bsharath
ID: 19548089
Thanks a lot got it.But how do i remove the outward back to inward.
Any way to get a color to red if date older than today's date.
Can it only accept this format (23-Jul-07)
0
 
LVL 45

Expert Comment

by:patrickab
ID: 19549319
bsharath,

>But how do i remove the outward back to inward.

You don't move them yourself - the macro does that. The items will only be returned to the first sheet when you have put a date in the 'Date returned' column on the 'Outward - lent' sheet. After you have put a date in the 'Date returned' column on the 'Outward - lent' sheet, the macro will run automatically.


>Any way to get a color to red if date older than today's date.

Yes, that is now included in the new file for the 'Outward - lent' sheet. However as the only date you have is the date you lent the CD the date is only highlighted red if the Date Lent plus the value you place in cell F2 on the 'Outward - lent' sheet is less than today's date.


>Can it only accept this format (23-Jul-07)

Yes. I have now defined the date format for those columns like 23-Jul-07

Here's the new file with those changes. The instructions are in the file.

http://my.storenow.net?f=1660

Hope that helps

Patrick
0
 
LVL 45

Accepted Solution

by:
patrickab earned 2000 total points
ID: 19550425
The previous file had an error in it - so here's a new file:

http://my.storenow.net?f=1661

The problem was the number of days to lend was on row 2 which would be deleted when the item on the same row was returned to the first Sheet. So I've moved the value to cell G1.
0
 
LVL 11

Author Comment

by:bsharath
ID: 19553058
Thanks a lot.I have some more points to add to this file but dont want to ask all in the same question.Shall i raise a new question and post the link to you.?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 19554799
If they are simple questions, then ask them here. If they require significant work then I suggest that you open a new question.
0
 
LVL 11

Author Comment

by:bsharath
ID: 19554812
0
 
LVL 45

Expert Comment

by:patrickab
ID: 19558735
bsharath - Thanks for the grade. I'll have a look at the other question later as we've got visitors right now. - Patrick
0
 
LVL 11

Author Comment

by:bsharath
ID: 19562185
Ok thanks...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

868 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