Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    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 advance
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Calculations (Excel 2K)

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

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Calculations (Excel 2K)

    <P ID="edit" class=small>(Edited by JohnBF on 27-Jul-04 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 + 14
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    5 Star Lounger
    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>

  5. #5
    2 Star Lounger
    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)

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    Uranium Lounger
    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    5 Star Lounger
    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>

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

  12. #12
    Uranium Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •