Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search marco to update a table
I have a code that should:
Look through all worksheets for Gl numbers that match those in the first sheet. Once it finds a number it needs to copy the adjecent amount in the correct place in the first sheet. The code needs to work its way through the whole work book which has many sheets. Also the tables may have gaps. None of the tables can be filtered and it has to work using a macro (i.e. no cell functions) This is the code i have so far: 'Define function to find end of table Function checklast() As Boolean Selection.End(xlDown).Select If ActiveCell.Address = "$A$65536" Then checklast = True Else checklast = False Exit Function Worksheets(nx).Range(add).Select End Function Sub Macro2() 'Define Variables Dim c As Object Dim WS As Worksheet Dim x As Integer Dim gl As Integer Dim amt As Integer Dim add As String Dim nx As String 'Loop through each worksheet except first one For Each WS In ActiveWorkbook.Sheets WS.Activate If WS.Name < "Sheet1" Then GoSub Process_data End If 'MsgBox "Looking at the sheet named: " & WS.Name Next WS 'Check if cell in current worksheet contains a value Process_data: x = 1 Do If WS.Cells(x, 1).Value < "" Then gl = WS.Cells(x, 1).Value amt = WS.Cells(x, 2).Value nx = WS.Name add = WS.Cells(x, 1).Address GoSub Process_detail End If x = x + 1 Loop Until checklast() = True Return 'Check if cell in first sheet contains a value equal to gl 'If one is found paste amt in adjecent cell Process_detail: Worksheets("Sheet1").Activate With Worksheets("Sheet1").Range("A1:A100") Set c = .Find(gl, LookIn:=xlValues) If Not c Is Nothing Then .Range(c.Address).Activate ActiveCell.Offset(0, 1).Activate ActiveCell.Value = amt End If End With Return End Sub Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a marco to update my spreadsheet. | Excel Programming | |||
marco for a pivot table and expansion | Excel Programming | |||
Pivot table marco problem! | Excel Programming | |||
marco after selection in PV table | Excel Programming | |||
Marco: Pivot Table | Excel Programming |