Excel - VBA Macro 1

[debbiezzzzz]

Hello,

I have to write a macro or vba code and add them as buttons on a worksheet for our users. Unfortunately, I really don't know excell VBA. (I've used Access VBA extensively, and VB, but not sure on the objects methods etc for Excel.)

Anyhow what I want to write is basically this...

The sheet is has about 10 colums of info, and one row for each "record". There are for categories of line items that each have their own counter. The ID for each record follows a naming convention. (AAA-001, AAA-002 etc for type AAA, BBB-001, BBB-002 etc. for type BBB and similar for Types CCC and DDD)

What I want to do is have four buttons. Each to ADD a new line for AAA or BBB or CCC or DDD. The macro will need to find the last AAA or BBB or CCC or DDD record and insert a new blank line, Add the new entry (CCC-004 for example) to column A and leave the cursor on Column B for this new record.

If the user accidently sorts by another column, I would maybe have to sort it by column A again first?

Column A might look like this in a typical scenario.

AAA-001
AAA-002
AAA-003
AAA-004
BBB-001
BBB-002
CCC-001
DDD-001
DDD-002

If the User clicks the (Add C record.) I would want a new line below CCC-001 added, and then Column A filled in with CCC-002 automatically.

Anyhow, if anyone can point me in the right direction to get some sample Excel VBA that might help, that would be great.

Thanks,

D

 

[SkipVought]

Hi,

I'd make a column that just has the LEFT 3 characters. Let's assume it's column AA.

Last Row...

dim lLastRow as long
lLastRow = Sheets(1).[A1].end(xldown).row +1

assuming that your Worksheet is the FIRST tab (Sheets(1)) and that A1 is contiguous with the entire table.

having the last row and assuming Button AAA...

With Sheets(1).Cells(lLastRow, "A")
   n = application.COUNTIF(Sheets(1).range("AA:AA"), "AAA")
   .Value = "AAA" & "-" & n
End with

for a start.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[SkipVought]

Here's a sample of a button click...

Sub AAA_Click()
    Dim lLastRow As Long
    With Sheets(1).[A1]
        If .CurrentRegion.Rows.Count = 1 Then
            lLastRow = 2
        Else
            lLastRow = .End(xlDown).Row + 1
        End If
    End With
    AddItm Sheets(1).Cells(lLastRow, "A"), "AAA"
End Sub

Each Button Click calls AddItm, with the appripriate String argument...

Sub AddItm(r As Range, sVal As String)
    Dim n
    n = Application.CountIf(r.Parent.Range("AA:AA"), sVal)
    If IsError(n) Then
        n = 1
    Else
        n = n + 1
    End If
    With r
        .Value = sVal & "-" & Format(n, "000")
        .Parent.Cells(.Row, "AA").Value = sVal
        With .Interior
            Select Case sVal
                Case "AAA"
                    .ColorIndex = 22
                Case "BBB"
                    .ColorIndex = 23
                Case "CCC"
                    .ColorIndex = 24
                Case "DDD"
                    .ColorIndex = 25
            End Select
        End With
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[debbiezzzzz]

That was a great help. I made some modifications to not need the AA column, and just have to figure out how to get it to insert into the right area. (Last line of sheet not always best place)

Thank you so much!

 

[SkipVought]

Why is "Last line of sheet not always best place" when you can SORT to get everything in order. Turn on your macro recorder and sort on column A. Then modify. Call from your AddItm procedure.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[debbiezzzzz]

Because they have spacing and graphics between the sections.

 

[SkipVought]

Nice to know ALL of the requirements.

I'd STILL keep ONE TABLE (a table is CONTIGUOUS data).

Then, on another sheet, report from the table in 4 separate sections spaced for charts.

Optionally, you could do it all in one table on the same sheet, where AAA-999, for instance, is the ROW for charts. Adjust the row height to suite.

BTW, I have several applications that have charts that get built into individual rows of data to display the data graphically.

Once you disconnect one part of a table (inserting enpty rows) that causes other processing problems.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[debbiezzzzz]

Sorry, I wasn't really trying to "hide" the requirements. I just had an idea of how I wanted to do it and was attempting to make a simplified example.

I DO appreciate your help on this. I guess I was just as interested in finding a resource of information on VBA for excel as I was in doing this particular project.

Thanks again for all your help.

 

[SkipVought]

Part of that body of information, is related to experience in processing data in worksheets. Chopped up data is harder to process than contiguous data. Take it from a grey head.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[xlbo]

...and from not quite so grey a head. Not having a go or anything - just backing up Skip's assertion that chopping up data is a bad idea. There is a difference between how you STORE data - nice contiguous tables and how you REPORT on data - however you want....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question