Results 1 to 12 of 12
Thread: VBA Calculations (Excel 2K)

20040727, 15:02 #1
 Join Date
 Feb 2001
 Location
 Louisville, Kentucky, USA
 Posts
 605
 Thanks
 0
 Thanked 0 Times in 0 Posts
VBA Calculations (Excel 2K)
Surely this is simple for those of you smart ones (that leaves me out)
What I would to do is the following:
if(g1="","",g1+14)
I want to do this in vb ... the results of the calculation would be for "h1". I want this calculation to run the entire column G and H.
Thanks in advanceRoberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

20040727, 15:11 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: VBA Calculations (Excel 2K)
Why would you want to do this in VBA? A formula is much more efficient.

20040727, 15:13 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: VBA Calculations (Excel 2K)
<P ID="edit" class=small>(Edited by JohnBF on 27Jul04 09:13. Misread the question.)</P>Do you have the loop written and just want the formula? Just the formula would be something like:
if not isempty(sourcecell) then targetcell.value = sourcecell.value + 14John ... I float in liquid gardens
UTC 7ąDS

20040727, 15:27 #4
 Join Date
 Feb 2001
 Location
 Louisville, Kentucky, USA
 Posts
 605
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VBA Calculations (Excel 2K)
Hans,
I've spoken of this in another post.....there is code I have that opens another workbook, copies cells in the current workbook, then pastes the cells in the newly opened workbook. However, if I protect the cells where the formulas are the code for open, copy, paste won't work. So at this point I can't protect the cells. (or not sure how to bypass that problem). And all is going well with the spreadsheet except recently the users are mistakenly deleting the formulas I've put in. So I'm attempting to put the formulas in VBA so they can't mistakenly get deleted.
Hope that answers your question.
I have the following code in the WorkSheet_Change
Private Sub Worksheet_Change(ByVal Target As Range)
For ltrUpdate = 1 To Range("G65536").End(xlUp).Row
If Not IsEmpty(Range("G" & ltrUpdate).Value) Then
Range("H" & ltrUpdate).Value = Range("g" & ltrUpdate).Value + 14
End If
Next ltrUpdate
End Sub
But it seems to run very slowly....I get the correct results....I think I have it in the wrong place in VBE. In this case the worksheet this needs to be in is "Asthma".
I want to include several other ones (formulas) once I get this one mastered)
Thanks for your help, as always it's appreciated.Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

20040727, 15:28 #5
 Join Date
 Jan 2002
 Location
 Missouri, USA
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VBA Calculations (Excel 2K)
For filling all of column H (as far as Column G extends)
Range("H1").AutoFill Destination:=Range("H1:H" & Range("G65536").End(xlUp).Row)

20040727, 15:47 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: VBA Calculations (Excel 2K)
For starters, change the code to something like this, untested:
If Not IsEmpty(Range("G" & ltrUpdate)) Then
With Range("G" & ltrUpdate)
.Offset(0,1).Value = .Value + 14
End With
End If
You'd probably be better off setting the range in the G column and then using a for each loop. Even better would be to use the SpecialCells() method on all of column G to set the range of cells to be operated on, then use a for each loop with offset; this way you won't need to check the IsEmpty Property. There are plenty of examples of using the SpecialCells Method in this Forum.
I also agree with Hans' & Shades comment that filling it with a formula will be faster.John ... I float in liquid gardens
UTC 7ąDS

20040727, 16:50 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: VBA Calculations (Excel 2K)
<hr>if I protect the cells where the formulas are the code for open, copy, paste won't work<hr>
Why not write your code for open, copy and paste so that they unprotect the worksheet and then reprotect it when done?
Steve

20040727, 18:09 #8
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VBA Calculations (Excel 2K)
First, let me repeat what I have said before, there is no reason that your cut/copy VBA code can not work if the worksheet is protected. The code just has to upnortect the worksheet, do the cut/copy and paste, the reprotect the worksheet. You are really going to a lot of trouble to solve a problem that does not exist.
Your code probably runs very slowly because it is in the Worksheet_Change event routine. This code will run when any cell on the worksheet has changed. There is nothing in the code to stop the loop from running if the cell that was changed does not affect what is in column G. In addition, each time through the loop, you change a cell on the worksheet. That is going to cause the routine to run again. It looks like this code should only run if a cell in column G has changed, and then it should only update the cells in column H that correspond to cells in column G that were changed. If you must use VBA to solve this, see if the code below does what you want and faster.
<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("G:G")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each oCell In Intersect(Target, Range("G:G"))
If oCell.Value <> "" Then
oCell.Offset(0, 1).Value = oCell.Value + 14
Else
oCell.Offset(0, 1).Value = ""
End If
Next oCell
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
</pre>
Legare Coleman

20040727, 18:15 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: VBA Calculations (Excel 2K)
As an alternative you could also use:
oCell.Offset(0, 1).ClearContents
instead of:
oCell.Offset(0, 1).Value = ""
To clear the cell, rather than putting a null in it.
Steve

20040727, 18:55 #10
 Join Date
 Feb 2001
 Location
 Louisville, Kentucky, USA
 Posts
 605
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VBA Calculations (Excel 2K)
OK....OK....OK....
I finally took the plunge and the advice that has been given to me so many times and added:
ActiveWorkbook.Unprotect Password:="*****"
before the actual code runs
to the existing open, copy, paste, close code
as well as
ActiveWorkbook.protect Password:="****"
after the code runs
and everyone is right and as always, I'M totally wrong....all works well.....
MY HUMBLE APOLOGIES TO ALL!!!!
AND MY SINCERE THANKS!!!Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

20040727, 19:38 #11
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: VBA Calculations (Excel 2K)
Please don't feel worse about it, but it also would have been helpful if you had started out with a note along the lines of "Continuing on from post #####"; it would have provided more context, as I wasn't aware that Legare had already advised you on the same project.
John ... I float in liquid gardens
UTC 7ąDS

20040728, 00:29 #12
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VBA Calculations (Excel 2K)
I think that this will greatly simplify your project. Glad you got that part working. If we can be of any further help let us know.
Legare Coleman