VBA Calculations (Excel 2K)

Topic

New Reply

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 advance

Reply | Quote

Replies

Why would you want to do this in VBA? A formula is much more efficient.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

[indent]

---

if I protect the cells where the formulas are the code for open, copy, paste won’t work

---

[/indent]

Why not write your code for open, copy and paste so that they unprotect the worksheet and then reprotect it when done?

Steve

Reply | Quote

[indent]

---

if I protect the cells where the formulas are the code for open, copy, paste won’t work

---

[/indent]

Why not write your code for open, copy and paste so that they unprotect the worksheet and then reprotect it when done?

Steve

Reply | Quote

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.

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!!!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!!!

Reply | Quote

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.

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

Reply | Quote

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.

Reply | Quote

Why would you want to do this in VBA? A formula is much more efficient.

Reply | Quote

(Edited by JohnBF on 27-Jul-04 09:13. Misread the question.)

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 + 14

Reply | Quote

For filling all of column H (as far as Column G extends)

Range(“H1”).AutoFill Destination:=Range(“H1:H” & Range(“G65536”).End(xlUp).Row)

Reply | Quote

For filling all of column H (as far as Column G extends)

Range(“H1”).AutoFill Destination:=Range(“H1:H” & Range(“G65536”).End(xlUp).Row)

Reply | Quote

(Edited by JohnBF on 27-Jul-04 09:13. Misread the question.)

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 + 14

Reply | Quote