Smithy1001
Programmer
Hi,
I use delphi to manipulate excel and frequently format cells iteratively. I am told that using say range['A1'].select and then excel.selection.value:= etc is time consuming and the best way is to put just range['A1'].value:= as the select process takes time. So, I have the following code which I wish to rif of the .select
for i := 0 to NumCodesMPSDetail-1 do
begin
Num1 := (13*i)+10;
WS.range['B'+inttostr(Num1+1)+':M'+inttostr(Num1+1)].select;
Excel.Selection.FormatConditions.Delete;
Excel.Selection.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4));
Excel.Selection.FormatConditions[1].Interior.ColorIndex := 7;
Excel.Selection.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
Excel.Selection.FormatConditions[2].Interior.ColorIndex := 3;
end;
I have put
for i := 0 to NumCodesMPSDetail-1 do
begin
Num1 := (13*i)+10;
RangeString := 'B'+inttostr(Num1+1)+':M'+inttostr(Num1+1);
Ranger := ws.range[rangestring];
ranger.FormatConditions.Delete;
ranger.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4)); //note put a but gives b...!
ranger.FormatConditions[1].Interior.ColorIndex := 7;
ranger.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
ranger.FormatConditions[2].Interior.ColorIndex := 3;
end;
where ranger is a variant. This works of sorts. Where I have put '=b$', my formatcondition actually comes out as '=a$' within the cells in excel. So I have 2 questions:
1. Why does the column reference b get transposed to a when I get rid of the select?
2. How can I use the with statement instead of having to write ranger. each time. ie. with ranger do begin formatconditions.delete; etc end;
Thanks
I use delphi to manipulate excel and frequently format cells iteratively. I am told that using say range['A1'].select and then excel.selection.value:= etc is time consuming and the best way is to put just range['A1'].value:= as the select process takes time. So, I have the following code which I wish to rif of the .select
for i := 0 to NumCodesMPSDetail-1 do
begin
Num1 := (13*i)+10;
WS.range['B'+inttostr(Num1+1)+':M'+inttostr(Num1+1)].select;
Excel.Selection.FormatConditions.Delete;
Excel.Selection.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4));
Excel.Selection.FormatConditions[1].Interior.ColorIndex := 7;
Excel.Selection.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
Excel.Selection.FormatConditions[2].Interior.ColorIndex := 3;
end;
I have put
for i := 0 to NumCodesMPSDetail-1 do
begin
Num1 := (13*i)+10;
RangeString := 'B'+inttostr(Num1+1)+':M'+inttostr(Num1+1);
Ranger := ws.range[rangestring];
ranger.FormatConditions.Delete;
ranger.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4)); //note put a but gives b...!
ranger.FormatConditions[1].Interior.ColorIndex := 7;
ranger.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
ranger.FormatConditions[2].Interior.ColorIndex := 3;
end;
where ranger is a variant. This works of sorts. Where I have put '=b$', my formatcondition actually comes out as '=a$' within the cells in excel. So I have 2 questions:
1. Why does the column reference b get transposed to a when I get rid of the select?
2. How can I use the with statement instead of having to write ranger. each time. ie. with ranger do begin formatconditions.delete; etc end;
Thanks