Results 1 to 6 of 6

Thread: VB - Excell Issues with a cell being deleted.

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2022
    Posts
    3

    VB - Excell Issues with a cell being deleted.

    Hello Every one.

    I hope some one may help me with a little issue i have with a VB/Macro embedded coding.

    I have a good friend who designed me a few years back a Excell document for my business, I unfortunatly cant get hold of him.

    We have a Sheet with various numbers in various sections and we use a code to copy them numbers from one box to another, each section is totalled up and then this number is used to calculate another sheet, unfortunatly when we use the copy information button it copy's every thing perfectly but it deletes out the total box, How can i go round to getting some one to maybe help me ( complete novis on this i have to add )

    I really could use some help

    Jeff

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: VB - Excell Issues with a cell being deleted.

    If you can create a sample spreadsheet (remove any company data, replace with fake sample data) and add a copy of that here as an attachment, someone might be able to take a look at it and maybe assist. You could also post the macro code that is used. In Excel, click on View on the Ribbon, then click Macros, View Macros, and then click the Edit botton. The VBA window will appear and you can then copy the code from the code window and paste it between code tags here.

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2022
    Posts
    3

    Re: VB - Excell Issues with a cell being deleted.

    Quote Originally Posted by jdc2000 View Post
    If you can create a sample spreadsheet (remove any company data, replace with fake sample data) and add a copy of that here as an attachment, someone might be able to take a look at it and maybe assist. You could also post the macro code that is used. In Excel, click on View on the Ribbon, then click Macros, View Macros, and then click the Edit botton. The VBA window will appear and you can then copy the code from the code window and paste it between code tags here.
    Hey, Thanks for your time.

    What happens is cell "I5 to I14" get moved to "F5 to F14" and then the coulm "G" Named Receipts get deleted, but its deleting the totals for each section so on this case in the photo below it deletes out "G16" i want the column deleted but not to remove this G16 box ( the box moves as you add more products into the sheet as products change

    I think this is the right code for what ive described.


    Private Sub GenerateSummaryColumns(SectionName As String)

    Dim BlockStartRow As Integer
    Dim BlockEndRow As Integer

    UnProtectSheet ("Stock Report")

    GetSectionExtent SectionName, BlockStartRow, BlockEndRow

    If BlockStartRow = -1 And BlockEndRow = -1 Then

    Application.Goto Reference:=SectionName

    SumString = "G" & BlockStartRow & ":G" & BlockEndRow
    ActiveCell.Offset(0, 6).Formula = "0" ' G
    ActiveCell.Offset(0, 6).NumberFormat = "#,##0.00_);[Red](#,##0.00)"

    SumString = "L" & BlockStartRow & ":L" & BlockEndRow
    ActiveCell.Offset(0, 11).Formula = "0" ' L
    ActiveCell.Offset(0, 11).NumberFormat = "_-\£* #,##0.00_-;[Red]-\£* (#,##0.00)_-"

    SumString = "N" & BlockStartRow & ":N" & BlockEndRow
    ActiveCell.Offset(0, 13).Formula = "0" ' N
    ActiveCell.Offset(0, 13).NumberFormat = "_-\£* #,##0.00_-;[Red]-\£* (#,##0.00)_-"

    SumString = "O" & BlockStartRow & ":O" & BlockEndRow
    ActiveCell.Offset(0, 14).Formula = "0" ' E
    ActiveCell.Offset(0, 14).NumberFormat = "_-\£* #,##0.00_-;[Red]-\£* (#,##0.00)_-"

    Else

    Application.Goto Reference:=SectionName

    SumString = "G" & BlockStartRow & ":G" & BlockEndRow
    ActiveCell.Offset(0, 6).Formula = "=SUM(" & SumString & ")" ' G
    ActiveCell.Offset(0, 6).NumberFormat = "#,##0.00_);[Red](#,##0.00)"

    If SectionName = "DraughtAles" Then
    SumString = "J" & BlockStartRow & ":J" & BlockEndRow
    ActiveCell.Offset(0, 9).Formula = "=SUM(" & SumString & ")" ' J
    ActiveCell.Offset(0, 9).NumberFormat = "#,##0.0_);[Red](#,##0.0)"
    SumString = "E" & BlockStartRow & ":E" & BlockEndRow
    ActiveCell.Offset(0, 4).Formula = "=AVERAGE(" & SumString & ")" ' J
    ActiveCell.Offset(0, 4).NumberFormat = "_-\£* #,##0.00_-;[Red]-\£* (#,##0.00)_-"
    End If

    SumString = "L" & BlockStartRow & ":L" & BlockEndRow
    ActiveCell.Offset(0, 11).Formula = "=SUM(" & SumString & ")" ' L
    ActiveCell.Offset(0, 11).NumberFormat = "_-\£* #,##0.00_-;[Red]-\£* (#,##0.00)_-"

    SumString = "N" & BlockStartRow & ":N" & BlockEndRow
    ActiveCell.Offset(0, 13).Formula = "=SUM(" & SumString & ")" ' N
    ActiveCell.Offset(0, 13).NumberFormat = "_-\£* #,##0.00_-;[Red]-\£* (#,##0.00)_-"

    SumString = "O" & BlockStartRow & ":O" & BlockEndRow
    ActiveCell.Offset(0, 14).Formula = "=SUM(" & SumString & ")" ' E
    ActiveCell.Offset(0, 14).NumberFormat = "_-\£* #,##0.00_-;[Red]-\£* (#,##0.00)_-"

    End If


    ' Protect the sheet again
    ProtectSheet ("Stock Report")

    End Sub


    Name:  fil.jpg
Views: 94
Size:  35.7 KB
    Last edited by Jeffog; Nov 9th, 2022 at 12:10 PM.

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: VB - Excell Issues with a cell being deleted.

    Some clarification is needed on what actually happens versus what you want to happen.

    If the Receipts column (G) gets deleted, of course the Total in that column would be deleted also. However, if that column is deleted, there would be no need for the Total from cell G16, since it would be 0 (zero).

    I am guessing that what you actually want to happen is for Column G to NOT be deleted, but just have the cell values G5:G14 cleared. If that is not correct, please post screen captures of the sheet before and after the macro has been run with before data and after data.

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2022
    Posts
    3

    Re: VB - Excell Issues with a cell being deleted.

    Thanks for your response, Yes sorry at the moment the Entire colum G from "G2" all the way to the bottom gets deleted. I only want the Cells in white to be deleted G5:G14 and for the Foruma in G16 to remain. There are other numbers that do need deleted from column "G" but i do not need these to be Totalled up as i do in the section called " Draught"

    Please see a little more of the sheet in question

    Attachment 186198Attachment 186198


    Name:  beforemacro.jpg
Views: 106
Size:  107.1 KB
    Name:  AFTER macro.jpg
Views: 105
Size:  107.1 KB
    Last edited by Jeffog; Nov 10th, 2022 at 04:04 AM.

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: VB - Excell Issues with a cell being deleted.

    OK, so it looks like the macro is not actually deleting Column G, just the data in it. The simple fix would be to have it not delete the contents of cell G16.

    The code you have posted does not appear to be all of the code in the set of macros. BlockStartRow and BlockEndRow look like they are being set by the GetSectionExtent Sub, but I do not see the code for that Sub. Once we can see what that Sub is doing, we can determine how to fix the issue, either by adjusting the range(s) affected or by just skipping cell G16.

Posting Permissions

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



Click Here to Expand Forum to Full Width