Posts by imported_Anonymous

    I'm using Office 2000 and I want a macro that inserts data into a particular column of a spreadsheet.

    I’ve done one that works for just so far, then it goes a bit odd.

    This is a (hopefully) simple explanation as the spreadsheet is a great deal bigger than this example.

    Spreadsheet contains info in column a to h and rows 2 to 20. The only column where input is not required is b. The data is sorted by column d then f then e. In column b each row is then given a ‘record number’.

    I recorded the macro – i.e.

    highlight whole spreadsheet,
    data sort (as above),
    go to b2
    press f8 to anchor selection and end down keys to last row
    go to b2 enter #1
    goto b3 enter +b2+1 press enter
    press ctrl c,
    goto b4,
    press f8 then use arrow keys to highlight columns b & c
    press end down keys (anchored to row c because that contains info in all rows, but row b is now empty below this point which means if I press f8 and end down every row in that column of the entire spreadsheet would then contain info),
    once both rows are highlighted to whatever row column c ends at (in this instance #20), use arrow keys to just highlight column b
    press enter.

    Now I’ve got 50 new records to input so I do, leaving column b empty until I’ve data sorted the info and can then renumber each record consecutively, including the new stuff.

    Run the macro and it only numbers rows 2 to 20 but it should (by my reckoning) have renumbered rows 2 to 69.

    I’m doing something wrong but I don’t know what – find it strange that the macro adjusts to include the whole spreadsheet for the data sort, but stays static by only including the original rows for the number sequencing. :confused:

    Any help would really really really be appreciated – that’s if anyone can even understand what I’m getting at – I definitely wouldn’t win any explanation of the year award !!!!!.

    I need to pull data from specific ranges on different pages, put it into Outlook 2000/XP, then send an E-Mail to different people based on the vaule of one of the ranges. HELP!! Any ideas???

    I believe the code at the end of the message performs the action I want
    (Poster previously I think by Dave)

    I extended this approach for functions with more than two arguments. (by altering the number passed to NBArgs and the strings to DescrArgs and Args) But I have run into problems when I have more than two new user-defined functions. I think this is due to the CharNextA parameter. It seems that whichever function I registered last with a call to CharNextA or CharPrevA overrides the function parameter definitions of the previous function registrations.

    I do not know what the purpose of CharNextA and CharPrevA are. I have tracked down that they are windows api calls from the user32.dll, but apart from that I am lost!

    Once again, any help would be great!



    Const Lib = """c:\windows\system\user32.dll"""
    Option Base 1

    Private Function Multiply(N1 As Double, N2 As Double) As Double
    Multiply = N1 * N2
    End Function


    Private Function Divide(N1 As Double, N2 As Double) As Double
    Divide = N1 / N2
    End Function


    Sub Auto_open()

    Register "DIVIDE", 3, "Numerator,Divisor", 1, "Division", _
    "Divides two numbers", """Numerator"",""Divisor """, "CharPrevA"
    Register "MULTIPLY", 3, "Number1,Number2", 1, "Multiplication", _
    "Multiplies two numbers", """First number"",""Second number """, _

    End Sub


    Sub Register(FunctionName As String, NbArgs As Integer, _
    Args As String, MacroType As Integer, Category As String, _
    Descr As String, DescrArgs As String, FLib As String)

    Application.ExecuteExcel4Macro _
    "REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") _
    & """,""" & FunctionName & """,""" & Args & """," & MacroType _
    & ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"

    End Sub


    Sub Auto_close()

    Dim FName, FLib
    Dim I As Integer
    FName = Array("DIVIDE", "MULTIPLY")
    FLib = Array("CharPrevA", "CharNextA")
    For I = 1 To 2
    With Application
    .ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
    .ExecuteExcel4Macro "REGISTER(" & Lib & _
    ",""CharPrevA"",""P"",""" & FName(I) & """,,0)"
    .ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
    End With

    End Sub

    So I'm working on this big macro in Module1 in VBA Editor and SOMEHOW, I overwrote my project with a month old "Backup of Project."

    I do confess, I think I opened this Backup to look at it, but I thought I closed it.

    To replace my current project with the Backup copy, wouldn't I have had to SAVE AS the backup with my current project name? So I would have had to type in this name in?

    And further, wouldn't I have had to answer the question, "Do you want to replace the file?" with a "Yes" click?

    None of these events happened, yet my project was replaced.

    Can anyone enlighten me?

    Perhaps I shouldn't be using VBA Editor?

    This happened to me about 2 months ago too and I still have no idea how this happens.

    While I am quite new to Windows Programming. I have written over 250,000 lines of FORTRAN over 15 years and used UNIX for over 10 years while an engineer at Pratt & Whitney.

    Never, have I had problems like this with mainframe/workstation platforms.

    Sorry for this stupid problem and rant,

    I have an Excel database consiting of email addresses. Everytime I click in a cell it brings me to Outlook to send an email. I can remove the hyperlink individually, but is there a way to remove it on the entire spreadsheet?


    Dear All,

    I am creating an excel add-in that defines a fair few user-defined-functions. I would like to be able to put these into a new category (name of my choosing) in the function wizard of excel. I would also like to include descriptions of the variables to be passed to the function. I am using excel 2002, though backwards compatibility with excel 2000 would be great :) I have search the net and have not managed to find a working solution…

    Many thanks for any help.


    I need exactly the same thing. I wrote a module that will detect the change in data. Now, all I need is a function to actually add the row. Here you go :
    Sub insertrow()

    Dim currwksht As Worksheet
    Dim sku10 As String

    Set currwksht = ActiveSheet

    For i = 2 To currwksht.Rows.Count Step 1

    sku10 = currwksht.Cells(i, 1)

    If i > 2 Then

    If sku10 <> prevsku10 Then

    End If

    End If

    prevsku10 = sku10


    End Sub

    Thank you for your research Jack, and to all others who replied. I am coming to the conclusion that we should be looking at a compiled application, as the formulas that calculate the customer's sell price must not show under any circumstances.
    Once again, thank you for your replies and time.:thumbcoo:

    I need to create a template and save it so I can enter information and simply go to the next page and type information (Same format for each page). Can't seem to get it to work. HELP