Posts by norie

Re: Unhide/ Hide Rows based on Cell Value issue

What's the problem?

Are you really checking the cells for the text values '1', '2', '3' etc?

If you aren't remove the "" around the numbers.

Re: ComboBox Table Column

Try this.

Dim rng As Range 
Dim cl As Range


    Set rng = Sheets("Validate1").ListObjects("Tbl_Choices").ListColumns(ComboBox1.Value).DataBodyRange 


    For Each cl In rng 
        If cl.Value <> "" Then
            ComboBox2.AddItem cl.Value 
        End If
    Next cl

Re: Shape needs to change color based on cell "word" value

Kenneth

Wouldn't a workbook level SheetChange be an idea?

Re: VBA Macro to Write Data into Text File

You could read the entire file in, split it on line breaks into an array and then loop through the array doing nothing until you find 'lambda'.

Once you've found 'lamda' you can continue on looping through the array but instead of doing nothing you output to an array.

Once you've finshed looping you can write to the destination file.

So that would be reading from a file once, processing the data in code, which will be quick, and then writing to a file once.

Re: Shape needs to change color based on cell "word" value

How are the boxes linked to the sheets?

Name? Caption?

Are the dropdowns on the sheet the same, eg same list and location?

Re: VBA Macro to Write Data into Text File

Why not read the data from the source file in in one go?

Then search/parse out the data you want, perhaps store it in array or even strung, and output it in one go to the destination file.

By the way, I don't see in the posted code where you are searching for 'lamda'.

PS If you could upload a sample file, or perhaps even before/after files, that would help.

Re: Select the Data range based on the 2 input date range provided

This can easily be solved - the variables for the dates in the SQL statement should not be in quotes.

Re: Store persistent information within a userform

You don't need to store the data as it's displayed and you don't need to store it in a cell, but you do need to store it somewhere.:)

Even if you were using cells there's no reason to have all the data in one cell.

Re: Store persistent information within a userform

If the data isn't to be entered in a cell where is it supposed to be stored?

Re: Unable to enter password in the webpage using VBA

Try this.

IE.document.all("password").Value = Left(ThisWorkbook.Sheets("XX").Range("C2").Value, 12)

Re: Programmatically adding code to ThisWorkbook

Have you considered having a blank 'template' workbook that has the same code in it but for the BeforeDoubleClick event rather than SheetBeforeDoubleClick event?

You could then copy that sheet, copy the required data to the copy and then save the copy with the data, and code.

Re: VBA IIf statement error since update to Office 2016

Oops, got the 0 part wrong - it was kind of late.:)

However when I run this code there's no error and the message box displays 2.5.

Dim n As Long
Dim z As Double


    n = 0


    z = 5 / IIf(n = 0, 2, n)
    
    MsgBox z

If you run this code the divide by zero error will pop up.

Dim n As Long
Dim z As Double


    n = 0
    
    z = IIf(n = 0, 5 / 2, 5 / n)
    
    MsgBox z

Re: SaveAs Failed - I think because of network UNC

Can you post the line of code rather than a picture?:)

PS One thing I notice in the picture is that you don't specify the FileFormat argument when saving.

Re: VBA IIf statement error since update to Office 2016

S O

The error here isn't because the 2 parts of the Iff statement are evaluated.:)

n = 0 
 
z = 5 / IIf(n = 0, 2, n)

It's simply because the Iif returns 0 and we get a division by zero.

Re: Programmatically adding code to ThisWorkbook

Will the new workbooks contain only one sheet?

Re: Format columns in an array

Dave

Do you want to retain the formatting on the sheet when you transfer the values to the listbox?

If you do you could try using the Text property.

With rngTarget 
        For lcol = 1 To .Columns.count 
            For lrw = 1 To .Rows.count 
                rcArray(lrw, lcol) = .Cells(lrw, lcol).Text
            Next lrw 
        Next lcol 
    End With

Re: Macro Line doesn't execute - no error message

What happens if you remove On Error Resume Next?

Re: Worksheet_SelectionChange not working in Excel 2016

Have you tried using Target instead of ActiveCell?

Re: Passing Parameters from VBScript to VBA

Have you tried not typing the arguments?

Sub NewQuery(Optional BoM, Optional Qty)

Re: Passing Parameters from VBScript to VBA

What exactly did change in the sub being called?

Also, what's the value of BoM when you get the error(s)?