Posts by WinteE

Re: Speeding Up VBA Code

This code puts 10 in cell A1 of each sheet in a workbook :

Sub Test()


For Each ws In ThisWorkbook.Worksheets
    ws.Range("A1").Value = 10
Next ws


End Sub

Re: Speeding Up VBA Code

Hi,

You have to use the last row with a value

i = ActiveSheet.Cells(ActiveSheet.Cells.Rows.Count, "A").End(xlUp).Row


Range("A2:U" & i)

Succes

Re: Using a reference to return a value 'n' columns and rows away from the search res

=OFFSET() is what you need.

Example for formula in cell A1 picking data from cell D10
=OFFSET(A1,9,3)

And if you really want it simple then you enter =D10 in cell A1

Re: Dynamic Grid Lines on a Chart

gridline .. ?????

Please post the file ?

Re: VBA Conditional Formatting

Set MyPlage = Range("G:G")

Re: Dynamic Grid Lines on a Chart

Create a source of the graph of just one week. Get the values from the whole set of data with =VLOOKUP() .

Success

Re: Lock/Unlock Cells Based On Validation Lists

Hi Ana,

As you wrote "remember I'm using Excel 2010" I don't know if this is what you need (using Excel 2003 version).

Private Sub Worksheet_Change(ByVal Target As Range)


    If Not Intersect(Target, Range("F11:F20")) Is Nothing Then
            
            ActiveSheet.Unprotect
               
        If Target.Value = "Active" Then
            Range("J" & Target.Row & ":P" & Target.Row).Select
            Selection.Locked = False
        Else
            Range("J" & Target.Row & ":P" & Target.Row).Select
            Selection.Locked = True
        End If
    End If
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


End Sub

Success

Erik

Re: Count Mouse Clicks On Cell

Hi,

I've tried several events :

Before right click -> Normally left click is used to select a cell
Before doubbleclick -> Directs to the contents of the cell, selection change required to leave the contents

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
    If Not Intersect(Target, Range("B4")) Is Nothing Then
        If IsNumeric(Range("B4").Value) Then
            Range("B4").Value = Range("B4").Value + 1
            Selection.Offset(1, 0).Select
        Else
            Range("B4").Value = 1
            Selection.Offset(1, 0).Select
        End If
    End If


End Sub

Adding a selection change completes it.

Erik

Re: Count Mouse Clicks On Cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Not Intersect(Target, Range("B4")) Is Nothing Then
        If IsNumeric(Range("B4").Value) Then
            Range("B4").Value = Range("B4").Value + 1
        Else
            Range("B4").Value = 1
        End If
    End If


End Sub

In Excel 2000 this is the only option that comes near. Don't know if a click event has been added to the Worksheet events. If there is you can use the same code in it.

Erik

Re: Error Within Nested For Loop

Try this :

Sub numberdetermine() 
    Dim number As Integer, a As Integer, b As Integer, c As Integer 
    a =  Range("AB36").Value 
    b = Range("AB37").Value 
    c = Range("AB38").Value 
    number = 0 
    For i = 1 To a 
        For j = 1 To b 
            For k = 1 To c 
                number = number + 1 
                Next k 
            Next j 
        Next i 
        Range("H8").Value = number 
        Range("H20").Value = number + 1 
        Range("H32").Value = number + 2 
    End Sub

Re: Filter Out Rows As They Become Blank

You've got to use VBA for this.

Record the filtering of sheet B with the Macro recorder. Copy this code into the Worksheet_Change event of the Master sheet.

Re: Lookup Function For Checking 2 Vectors

Join the two condition items together in a hidden column. Now you can use this column for a VLOOKUP() function.

Re: Selectively Importing Text

You need to work with the Input function to do so. With this function you can import the rows of the textfile one by one.

Re: Automatically Refresh Autofilter

Then you will have to link the code to a key combination.

Re: Lookup Function Error

The code I posted works fine on my side.

Re: Lookup Function Error

When using VLOOKUP with numbers I always use cells formatted as TEXT. The reason for this is that if the numbers in the SearchColumn aren't sorted you often get the wrong data.

Re: Lookup Function Error

Public Function VHLOOKUP(SearchRange As Range, SearchVertical As String, SearchHorizontal As String) As Variant 
     
    Dim SearchColumn As Variant 
    Dim SearchRow As Variant 
    Dim ColumnCount As Integer 
    Dim RowCount As Integer 
    Dim FindColumn As Integer 
    Dim FindRow As Integer 
     
    ColumnCount = 0 
    RowCount = 0 
     
    For Each SearchColumn In SearchRange.Columns 
        ColumnCount = ColumnCount + 1 
        If UCase(SearchColumn.Columns.Cells(1, 1).Value) = UCase(SearchHorizontal) Then 
            FindColumn = ColumnCount 
        End If 
    Next SearchColumn 
     
    For Each SearchRow In SearchRange.Rows 
        RowCount = RowCount + 1 
        If UCase(SearchRow.Rows.Cells(1, 1).Value) = UCase(SearchVertical) Then 
            FindRow = RowCount 
        End If 
    Next SearchRow 
     
    If FindColumn = 0 Or FindRow = 0 Then 
        VHLOOKUP = 0 
    Else 
        VHLOOKUP = SearchRange.Cells(FindRow, FindColumn).Value 
    End If 
     
End Function

Insert this code into a module of the workbook, and add a header to column B (e.g. "Town"). The code gives you an 'User defined function' for a combination of VLOOKUP and HLOOKUP.

Re: Lookup Function Error

Natalie,

Are the formats of both SearchValue-field and SearchColomn the same ?

Change both formats to "Text".

Erik

Re: Lookup Function Error

Hi Natalie,

I'm having no trouble with this after opening your attached file.

You could try using VLOOKUP instead i your problem remains.

Erik

Re: Automatically Refresh Autofilter

Put the next code into the Open event of the worksheet containing the filter :

Selection.AutoFilter Field:=1, Criteria1:="Whatever"

and the following code into the Change event of the worksheet you get the data from the web :

Worksheets("Sheet1").Activate
Selection.AutoFilter Field:=1, Criteria1:="Whatever"

Erik