Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Wht is this Code not Working ?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Wht is this Code not Working ?
Could anyone explain why a particular part of this code (see below) does not
run


Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop


despite the fact that I have the same code which executes as a Workbook_Open
routine within the module ThisWorkbook

The code should copy the formula in S2 down until the last value in Column
7. I sometimes want to refresh the data from the database. Currently the
database data is retrived only on open.

Thanks



Sub Refresh_Timepoint()

Sheets("Database").Select
Cells.Select
Selection.ClearContents


Range("A1").Select
Sheets("Database").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDir =C:\timepoint;Driver={Micr
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _

"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Saf eTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.DeptNum, Employees.PayrollNum,
Employees.ContractType, Employees.EmployeeType, Employees.Forename,
Employees.Surname, Employees.EmpAddress1, Employees.EmpAddress2," _
, _
" Employees.EmpAddress3, Employees.EmpAddress4,
Employees.DateOfBirth, Employees.TerminationDate,
Employees.TerminationPeriod, Employees.CommencementDate,
Employees.CommencementPeriod, Employees.PayRat" _
, _
"e, Employees.NatInsNum" & Chr(13) & "" & Chr(10) & "FROM
`C:\timepoint\Timepoint_be`.Employees Employees" & Chr(13) & "" & Chr(10) &
"ORDER BY Employees.Surname" _
)
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

End With
Range("A1").Select

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Database").Select

Range("A1").Select

Columns("L:M").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("N:N").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("o:o").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("P:P").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.NumberFormat = "?#,##0.00"



Columns("B:B").Select
Selection.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

Columns("D:D").Select
Selection.Replace What:="10", Replacement:="Crew F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="11", Replacement:="Crew P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="12", Replacement:="Mgr F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="13", Replacement:="Mgr P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

ActiveWorkbook.PrecisionAsDisplayed = False
Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"

Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Database").Select
Range("A1").Select
End Sub


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Wht is this Code not Working ?
It worked fine for me.

What do you mean by does not run?

Do you have values in G2:G?

--
Regards,
tom Ogilvy

"John" wrote in message
...
Could anyone explain why a particular part of this code (see below) does
not
run


Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop


despite the fact that I have the same code which executes as a
Workbook_Open
routine within the module ThisWorkbook

The code should copy the formula in S2 down until the last value in Column
7. I sometimes want to refresh the data from the database. Currently the
database data is retrived only on open.

Thanks



Sub Refresh_Timepoint()

Sheets("Database").Select
Cells.Select
Selection.ClearContents


Range("A1").Select
Sheets("Database").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _


"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDir =C:\timepoint;Driver={Micr
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _


"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Saf eTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.DeptNum,
Employees.PayrollNum,
Employees.ContractType, Employees.EmployeeType, Employees.Forename,
Employees.Surname, Employees.EmpAddress1, Employees.EmpAddress2," _
, _
" Employees.EmpAddress3, Employees.EmpAddress4,
Employees.DateOfBirth, Employees.TerminationDate,
Employees.TerminationPeriod, Employees.CommencementDate,
Employees.CommencementPeriod, Employees.PayRat" _
, _
"e, Employees.NatInsNum" & Chr(13) & "" & Chr(10) & "FROM
`C:\timepoint\Timepoint_be`.Employees Employees" & Chr(13) & "" & Chr(10)
&
"ORDER BY Employees.Surname" _
)
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

End With
Range("A1").Select

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Database").Select

Range("A1").Select

Columns("L:M").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("N:N").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("o:o").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("P:P").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.NumberFormat = "?#,##0.00"



Columns("B:B").Select
Selection.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

Columns("D:D").Select
Selection.Replace What:="10", Replacement:="Crew F/T", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="11", Replacement:="Crew P/T", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="12", Replacement:="Mgr F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="13", Replacement:="Mgr P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

ActiveWorkbook.PrecisionAsDisplayed = False
Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"

Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Database").Select
Range("A1").Select
End Sub




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Wht is this Code not Working ?
Hi Tom

I certainly do have values in G2 through G49. It retrives all the database
data required, pops the first formula in S2 but doesn't copy down (S2
combines the first and surname in F & G). It works perfectly when I open the
workbook as explained but not when called within a module - exact copy below
in first post




"Tom Ogilvy" wrote in message
...
It worked fine for me.

What do you mean by does not run?

Do you have values in G2:G?

--
Regards,
tom Ogilvy

"John" wrote in message
...
Could anyone explain why a particular part of this code (see below) does
not
run


Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop


despite the fact that I have the same code which executes as a
Workbook_Open
routine within the module ThisWorkbook

The code should copy the formula in S2 down until the last value in
Column
7. I sometimes want to refresh the data from the database. Currently the
database data is retrived only on open.

Thanks



Sub Refresh_Timepoint()

Sheets("Database").Select
Cells.Select
Selection.ClearContents


Range("A1").Select
Sheets("Database").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _



"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDir =C:\timepoint;Driver={Micr
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _



"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Saf eTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.DeptNum,
Employees.PayrollNum,
Employees.ContractType, Employees.EmployeeType, Employees.Forename,
Employees.Surname, Employees.EmpAddress1, Employees.EmpAddress2," _
, _
" Employees.EmpAddress3, Employees.EmpAddress4,
Employees.DateOfBirth, Employees.TerminationDate,
Employees.TerminationPeriod, Employees.CommencementDate,
Employees.CommencementPeriod, Employees.PayRat" _
, _
"e, Employees.NatInsNum" & Chr(13) & "" & Chr(10) & "FROM
`C:\timepoint\Timepoint_be`.Employees Employees" & Chr(13) & "" &
Chr(10)
&
"ORDER BY Employees.Surname" _
)
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

End With
Range("A1").Select

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Database").Select

Range("A1").Select

Columns("L:M").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("N:N").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("o:o").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("P:P").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.NumberFormat = "?#,##0.00"



Columns("B:B").Select
Selection.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

Columns("D:D").Select
Selection.Replace What:="10", Replacement:="Crew F/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="11", Replacement:="Crew P/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="12", Replacement:="Mgr F/T", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="13", Replacement:="Mgr P/T", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False

ActiveWorkbook.PrecisionAsDisplayed = False
Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"

Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Database").Select
Range("A1").Select
End Sub






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 863
Default Wht is this Code not Working ?
You way it doesn't run. What specifically do you mean by that? It won't
compile? It is never executed? It produces a run-time error?

If the latter, "S2.S2" is not a reference to cell S2. It's interpreted as a
reference to a named range having the name "S2.S2". The problem is, "S2.S2"
isn't a valid range name (names can't contain periods).

Is this simply a typo, i.e. it should be Range("S2") ???

On Sun, 5 Dec 2004 21:14:36 -0000, "John" wrote:

Could anyone explain why a particular part of this code (see below) does not
run


Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop


despite the fact that I have the same code which executes as a Workbook_Open
routine within the module ThisWorkbook

The code should copy the formula in S2 down until the last value in Column
7. I sometimes want to refresh the data from the database. Currently the
database data is retrived only on open.

Thanks



Sub Refresh_Timepoint()

Sheets("Database").Select
Cells.Select
Selection.ClearContents


Range("A1").Select
Sheets("Database").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDi r=C:\timepoint;Driver={Micr
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _

"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Sa feTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.DeptNum, Employees.PayrollNum,
Employees.ContractType, Employees.EmployeeType, Employees.Forename,
Employees.Surname, Employees.EmpAddress1, Employees.EmpAddress2," _
, _
" Employees.EmpAddress3, Employees.EmpAddress4,
Employees.DateOfBirth, Employees.TerminationDate,
Employees.TerminationPeriod, Employees.CommencementDate,
Employees.CommencementPeriod, Employees.PayRat" _
, _
"e, Employees.NatInsNum" & Chr(13) & "" & Chr(10) & "FROM
`C:\timepoint\Timepoint_be`.Employees Employees" & Chr(13) & "" & Chr(10) &
"ORDER BY Employees.Surname" _
)
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

End With
Range("A1").Select

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Database").Select

Range("A1").Select

Columns("L:M").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("N:N").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("o:o").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("P:P").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.NumberFormat = "?#,##0.00"



Columns("B:B").Select
Selection.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

Columns("D:D").Select
Selection.Replace What:="10", Replacement:="Crew F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="11", Replacement:="Crew P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="12", Replacement:="Mgr F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="13", Replacement:="Mgr P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

ActiveWorkbook.PrecisionAsDisplayed = False
Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"

Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Database").Select
Range("A1").Select
End Sub


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Wht is this Code not Working ?
As written, it expects a formula to already be in S2.

Range("S2").Formula = "=F2 & " " & G2
Range("S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi Tom

I certainly do have values in G2 through G49. It retrives all the database
data required, pops the first formula in S2 but doesn't copy down (S2
combines the first and surname in F & G). It works perfectly when I open
the
workbook as explained but not when called within a module - exact copy
below
in first post




"Tom Ogilvy" wrote in message
...
It worked fine for me.

What do you mean by does not run?

Do you have values in G2:G?

--
Regards,
tom Ogilvy

"John" wrote in message
...
Could anyone explain why a particular part of this code (see below)
does
not
run


Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop


despite the fact that I have the same code which executes as a
Workbook_Open
routine within the module ThisWorkbook

The code should copy the formula in S2 down until the last value in
Column
7. I sometimes want to refresh the data from the database. Currently
the
database data is retrived only on open.

Thanks



Sub Refresh_Timepoint()

Sheets("Database").Select
Cells.Select
Selection.ClearContents


Range("A1").Select
Sheets("Database").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _




"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDir =C:\timepoint;Driver={Micr
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _




"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Saf eTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.DeptNum,
Employees.PayrollNum,
Employees.ContractType, Employees.EmployeeType, Employees.Forename,
Employees.Surname, Employees.EmpAddress1, Employees.EmpAddress2," _
, _
" Employees.EmpAddress3, Employees.EmpAddress4,
Employees.DateOfBirth, Employees.TerminationDate,
Employees.TerminationPeriod, Employees.CommencementDate,
Employees.CommencementPeriod, Employees.PayRat" _
, _
"e, Employees.NatInsNum" & Chr(13) & "" & Chr(10) & "FROM
`C:\timepoint\Timepoint_be`.Employees Employees" & Chr(13) & "" &
Chr(10)
&
"ORDER BY Employees.Surname" _
)
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

End With
Range("A1").Select

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Database").Select

Range("A1").Select

Columns("L:M").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("N:N").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("o:o").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("P:P").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.NumberFormat = "?#,##0.00"



Columns("B:B").Select
Selection.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

Columns("D:D").Select
Selection.Replace What:="10", Replacement:="Crew F/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="11", Replacement:="Crew P/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="12", Replacement:="Mgr F/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="13", Replacement:="Mgr P/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False

ActiveWorkbook.PrecisionAsDisplayed = False
Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"

Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Database").Select
Range("A1").Select
End Sub








Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Wht is this Code not Working ?
?range("S2.S2").Address
$S$2

S2.S2 is a Lotus 1-2-3 style and is recognized by Excel.

--
Regards,
Tom Ogilvy




"Myrna Larson" wrote in message
...
You way it doesn't run. What specifically do you mean by that? It won't
compile? It is never executed? It produces a run-time error?

If the latter, "S2.S2" is not a reference to cell S2. It's interpreted as
a
reference to a named range having the name "S2.S2". The problem is,
"S2.S2"
isn't a valid range name (names can't contain periods).

Is this simply a typo, i.e. it should be Range("S2") ???

On Sun, 5 Dec 2004 21:14:36 -0000, "John" wrote:

Could anyone explain why a particular part of this code (see below) does
not
run


Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop


despite the fact that I have the same code which executes as a
Workbook_Open
routine within the module ThisWorkbook

The code should copy the formula in S2 down until the last value in
Column
7. I sometimes want to refresh the data from the database. Currently the
database data is retrived only on open.

Thanks



Sub Refresh_Timepoint()

Sheets("Database").Select
Cells.Select
Selection.ClearContents


Range("A1").Select
Sheets("Database").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _


"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDi r=C:\timepoint;Driver={Mic
r
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _


"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Sa feTransactions=0;Threads=3
;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.DeptNum,
Employees.PayrollNum,
Employees.ContractType, Employees.EmployeeType, Employees.Forename,
Employees.Surname, Employees.EmpAddress1, Employees.EmpAddress2," _
, _
" Employees.EmpAddress3, Employees.EmpAddress4,
Employees.DateOfBirth, Employees.TerminationDate,
Employees.TerminationPeriod, Employees.CommencementDate,
Employees.CommencementPeriod, Employees.PayRat" _
, _
"e, Employees.NatInsNum" & Chr(13) & "" & Chr(10) & "FROM
`C:\timepoint\Timepoint_be`.Employees Employees" & Chr(13) & "" & Chr(10)
&
"ORDER BY Employees.Surname" _
)
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

End With
Range("A1").Select

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Database").Select

Range("A1").Select

Columns("L:M").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("N:N").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("o:o").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("P:P").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.NumberFormat = "?#,##0.00"



Columns("B:B").Select
Selection.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

Columns("D:D").Select
Selection.Replace What:="10", Replacement:="Crew F/T", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="11", Replacement:="Crew P/T", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="12", Replacement:="Mgr F/T", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="13", Replacement:="Mgr P/T", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False

ActiveWorkbook.PrecisionAsDisplayed = False
Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"

Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Database").Select
Range("A1").Select
End Sub




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default Why is this Code not Working ?
Hi

It places a formula in S2 alright but doesn't copy it down. When I run a
separate module (only requesting a formula to be placed in S2 and then
copied down to the last row where there is data in G) with the exact same
code it runs fine, I've tried to call in within the Refresh_Timepoint module
but again it does nothing other than place the first formula (as required)
in to S2, then stops

I've run the code on Excel 2000 and 2002 with the same result

I'm baffled!


"Tom Ogilvy" wrote in message
...
As written, it expects a formula to already be in S2.

Range("S2").Formula = "=F2 & " " & G2
Range("S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi Tom

I certainly do have values in G2 through G49. It retrives all the
database
data required, pops the first formula in S2 but doesn't copy down (S2
combines the first and surname in F & G). It works perfectly when I open
the
workbook as explained but not when called within a module - exact copy
below
in first post




"Tom Ogilvy" wrote in message
...
It worked fine for me.

What do you mean by does not run?

Do you have values in G2:G?

--
Regards,
tom Ogilvy

"John" wrote in message
...
Could anyone explain why a particular part of this code (see below)
does
not
run


Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop


despite the fact that I have the same code which executes as a
Workbook_Open
routine within the module ThisWorkbook

The code should copy the formula in S2 down until the last value in
Column
7. I sometimes want to refresh the data from the database. Currently
the
database data is retrived only on open.

Thanks



Sub Refresh_Timepoint()

Sheets("Database").Select
Cells.Select
Selection.ClearContents


Range("A1").Select
Sheets("Database").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _





"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDir =C:\timepoint;Driver={Micr
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _





"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Saf eTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.DeptNum,
Employees.PayrollNum,
Employees.ContractType, Employees.EmployeeType, Employees.Forename,
Employees.Surname, Employees.EmpAddress1, Employees.EmpAddress2," _
, _
" Employees.EmpAddress3, Employees.EmpAddress4,
Employees.DateOfBirth, Employees.TerminationDate,
Employees.TerminationPeriod, Employees.CommencementDate,
Employees.CommencementPeriod, Employees.PayRat" _
, _
"e, Employees.NatInsNum" & Chr(13) & "" & Chr(10) & "FROM
`C:\timepoint\Timepoint_be`.Employees Employees" & Chr(13) & "" &
Chr(10)
&
"ORDER BY Employees.Surname" _
)
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

End With
Range("A1").Select

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Database").Select

Range("A1").Select

Columns("L:M").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("N:N").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("o:o").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("P:P").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.NumberFormat = "?#,##0.00"



Columns("B:B").Select
Selection.Replace What:="1", Replacement:="Crew", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False

Columns("D:D").Select
Selection.Replace What:="10", Replacement:="Crew F/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="11", Replacement:="Crew P/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="12", Replacement:="Mgr F/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="13", Replacement:="Mgr P/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False

ActiveWorkbook.PrecisionAsDisplayed = False
Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"

Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Database").Select
Range("A1").Select
End Sub










Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Why is this Code not Working ?
It places a formula in S2 alright
Please identify the line of code in the code you show below that puts a
formula in S2:

Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

--
Regards,
Tom Ogilvy


"John" wrote in message
...
Hi

It places a formula in S2 alright but doesn't copy it down. When I run a
separate module (only requesting a formula to be placed in S2 and then
copied down to the last row where there is data in G) with the exact same
code it runs fine, I've tried to call in within the Refresh_Timepoint
module
but again it does nothing other than place the first formula (as required)
in to S2, then stops

I've run the code on Excel 2000 and 2002 with the same result

I'm baffled!


"Tom Ogilvy" wrote in message
...
As written, it expects a formula to already be in S2.

Range("S2").Formula = "=F2 & " " & G2
Range("S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi Tom

I certainly do have values in G2 through G49. It retrives all the
database
data required, pops the first formula in S2 but doesn't copy down (S2
combines the first and surname in F & G). It works perfectly when I
open
the
workbook as explained but not when called within a module - exact copy
below
in first post




"Tom Ogilvy" wrote in message
...
It worked fine for me.

What do you mean by does not run?

Do you have values in G2:G?

--
Regards,
tom Ogilvy

"John" wrote in message
...
Could anyone explain why a particular part of this code (see
below)
does
not
run


Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop


despite the fact that I have the same code which executes as a
Workbook_Open
routine within the module ThisWorkbook

The code should copy the formula in S2 down until the last value
in
Column
7. I sometimes want to refresh the data from the database.
Currently
the
database data is retrived only on open.

Thanks



Sub Refresh_Timepoint()

Sheets("Database").Select
Cells.Select
Selection.ClearContents


Range("A1").Select
Sheets("Database").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _






"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDir =C:\timepoint;Driver={Micr
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _






"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Saf eTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.DeptNum,
Employees.PayrollNum,
Employees.ContractType, Employees.EmployeeType,
Employees.Forename,
Employees.Surname, Employees.EmpAddress1, Employees.EmpAddress2,"
_
, _
" Employees.EmpAddress3, Employees.EmpAddress4,
Employees.DateOfBirth, Employees.TerminationDate,
Employees.TerminationPeriod, Employees.CommencementDate,
Employees.CommencementPeriod, Employees.PayRat" _
, _
"e, Employees.NatInsNum" & Chr(13) & "" & Chr(10) & "FROM
`C:\timepoint\Timepoint_be`.Employees Employees" & Chr(13) & "" &
Chr(10)
&
"ORDER BY Employees.Surname" _
)
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

End With
Range("A1").Select

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Database").Select

Range("A1").Select

Columns("L:M").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("N:N").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("o:o").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("P:P").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.NumberFormat = "?#,##0.00"



Columns("B:B").Select
Selection.Replace What:="1", Replacement:="Crew",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="99", Replacement:="Mgr",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False

Columns("D:D").Select
Selection.Replace What:="10", Replacement:="Crew F/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="11", Replacement:="Crew P/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="12", Replacement:="Mgr F/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="13", Replacement:="Mgr P/T",
LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False

ActiveWorkbook.PrecisionAsDisplayed = False
Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"

Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Database").Select
Range("A1").Select
End Sub












Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Code not working and can't see why Steve Excel Discussion (Misc queries) 3 December 31st 04 03:12 PM
VB code is not working like it should Bob Reynolds[_3_] Excel Programming 8 July 19th 04 12:02 AM
Code not Working - Help please Brian Excel Programming 2 November 18th 03 10:58 PM
Code not working Bob Phillips[_5_] Excel Programming 5 August 14th 03 03:12 PM
For Each Code Not Working jacqui[_2_] Excel Programming 4 July 29th 03 02:44 AM


All times are GMT +1. The time now is 07:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement