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
    delete,
    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 !!!!!.
    :puzzled:

    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!


    Philip


    -----------------------


    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 """, _
    "CharNextA"


    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
    Next


    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,
    Tom

    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?


    Thanks!

    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.


    Philip

    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


    Next


    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