Posts by smuzoen

    Re: Excel VBA Array Problem


    As StephenR said you create a 2-D array - to make it easier you can transpose it into a 1-D array

    Code
    allSites = Application.Transpose(Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))
    MsgBox allSites(siteLoop)

    Re: output to empty cell range..


    I am assuming you have another function called IsAlphaNumeric? This determines if the string is composed of only alphabet (a-z) and/or numbers? I assume you are trying to write to Sheet2 in column L? If no to any of these then you need to be more specific or supply a sample workbook - you could try something like

    Re: Converting Data into number Data


    Where or how do you get the value in the first place - is it an import from a file? Unusual character to have in the cell. As cytop said you have ASCII 160 - if you want the specific format you could
    =TEXT(SUBSTITUTE(SUBSTITUTE(B3,CHAR(160),"")," ",""),"#,##00")


    However this is a text - cytop has a better solution in that you should format the cell.

    Re: Color Macro $30


    Just as an option to make the code nice and succinct and promote reuse of code

    Re: Color Macro $30


    Could you succinctly and clearly outline what further work has to be done to finalise this development? Does the code posted by Smallman satisfy yor requirements. ? If not what is the problem with the current code? To save a lot of time reading 30+ posts if you could briefly and clearly state what remains to be done, what modifications to the existing code are required to satisfy your requirements then perhaps this development may be finalised.

    Re: how to crate Login form in Excel


    Just a few notes - Excel security is extremely poor - if you are storing very sensitive information then I would not recommend Excel as a potential solution for you. However...for the "average user" it would probably suffice. If all the information is stored within Excel, even if the sheet is hidden (or very hidden) and it is of a sensitive nature, then password protecting the workbook would be required. However the problem is Excel and security - to break the password is not difficult but as previously stated...for keeping out the average user it may suffice. It depends on how personal the information is that you intend to record within the document.
    As with any problem - consider if the tools are adequate for your requirements.

    Re: Counting with multiple criteria without user pivot tables


    Have to agree with Smallman - another option is Sumproduct - why make it difficult unless you particularly want to learn VBA or make this an exercise in VBA coding.
    If I can offer you one piece of advice - if the Result table is going to be used elsewhere as a source of data ditch the merged cells - merged cells are evil and will cause you nothing but grief. Merged cells should be banned (IMO)


    Usage Sumproduct
    =SUMPRODUCT(--($B$4:$B$14="A"),--($C$4:$C$14="buy"),--($D$4:$D$14=$B$18))
    See attached workbook

    Re: Userform edit file multirows


    Exactly as Roy said - you are getting errors because RowFound1,RowFound2 etc have no value so

    Code
    .Cells(RowFound1, "M") = ritem1.Text
    'is the same as
    .Cells(null, "M") = ritem1.Text


    These variables "appear to be declared" but never assigned a value - if excel does not know what row "RowFound1" refers to how can the value from the textbox be entered into the worksheet. In actual fact these variables are NOT declared as far as the code is concerned. Is this just cut and pasted from some other code? Did they assign values somewhere to these variables (and these variables should be declared within the Button Click event not outside as Roy has already pointed out). You probably need to get back to some basics and understand what is going on with the code.

    Re: Macro won't stop creating zipped files


    Without seeing the workbook you have a loop

    Code
    For I = 2 To RowCount + 1


    Depending on the length of Column C you have the code for creating the zip file in a loop - that is why you ar getting multiple zip files created - the code for creating the zip files is within a loop - any reason for that? Why put directory into array FName then test if it is an array? Have you cut and pasted this code from somewhere? You loop variable is I then you reset the value of loop variable and then change its value which is really poor programming practice. To tell you the truth it is not the greatest bit of code.

    Re: move several columns and sort according to the one particular column


    Try - See attached workbook - if I understand your logic the following should do what you require

    Re: hard queston about vb


    You cannot do that - you could open the workbook containing the worksheet via vba code.

    Code
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = Workbooks.Open("C:\Temp\Book2.xlsx")
    Set ws = wb.Worksheets("Mainreport")
    wb.Activate
        If Me.TextBox51.Text <> "" Then
    ....


    However if the value does not exist on VLookUp you will get an error so you will need to use something like

    Code
    On Error Resume Next
    ......
    if err.Number<>0 then
    ....

    Re: Excel VBA With Range --- End With string manupulation help


    Quote

    If I put MsgBox .value inside the block it comes up with type miss match error.


    How are you returning a value from a MsgBox?

    Code
    x = MsgBox("Continue", vbYesNo)


    Quote

    get rid of any "," at the end of all strings


    Code
    If Mid(x, Len(x), 1) = "," Then
        x = Replace(x, x, Mid(x, 1, Len(x) - 1))
    End If
    MsgBox x


    Quote

    reduce the font size by 1pt (now its 8) if the lenght of the string is over 43


    Test for length with (you would probably have to loop through range)

    Code
    If Len(Range(....)) > 43 then
    Range(...).Font.Size = 7


    Quote

    split up the text to different cells if the string lenght is over 43 and there is a coma in the string and the adjacent cell to the right is empty


    I am using static ranges however you will get the point - I assume you want it split at comma (and assume only one comma)

    Code
    Dim varArr As Variant
    If Len(Range("G6")) > 43 And InStr(Range("G6"), ",") > 0 And Range("G6").Offset(, 1) = vbNullString Then
        varArr = Split(Range("G6"), ",")
        Range("G6") = varArr(0)
        Range("H6") = varArr(1)
    End If

    Re: vba Binary code to read text file &amp; search particular word &amp; amount associated wi


    You don't have a binary file however you can open a text file for Binary Access as snb has done (or Sequential/Random access). As cytop said you either have a binary file or a text file - they are NOT the same thing. There are certain advantages to binary files which you can find with Google. Why you want this opened for binary access I do not know. To do what you want you can open the text file and test each line to see if your name is present then use a regular expression to write out the results. See attached workbook. I have only used a very basic regular expression pattern however again Google is your friend is you want to be more specific wrt currency format.

    Re: I want to create labelling by using macro kindly help


    I have changed this so that you enter the start label and then enter the final NUMBER of the label - e.g. Starting Label A0006, Final Number 600 - see attached workbook

    Re: Search and Replace text


    You can upload a sample workbook when you create a post. When you post to thread Select "Go Advanced" and select your workbook and attach to the post. I will PM my email address and you can send the workbook by email if you prefer. The 10% must be paid to Ozgrid via PayPal before any solution can be developed. Once you have paid the 10% please confirm by posting into thread that it has been paid. The time frame you quoted is fine. It is late here so I will check thread in morning. I will PM my email to you now.