Vba not working afetr adjusting rows and columns

[sunboy]

Hi

I am kindly asking for help on VBA code. The code was working well but when I adjusted rows and columns of my worksheets its no longer working. Please not that there are two sales data in one sheet: one is for ZW$ (Column G to Y) and one for US$ (Column AH to AZ). Customer statements are two in one sheet, one for ZW$ and the other one for US$ and the same applies with opening balances.

Please find the attached worksheet including VBA code.


Thank you.

Regards,

Sunboy

[maniacb]

This file contains no VBA code to review.

[sunboy]

Hi

My apologies I have attached but it is not showing. Below is the code.

Thank you.

Sub sintek()
Dim ShtArr, Temp(), Data, i As Long, ii As Long, x As Long, Cust As String, dt As Date
Application.ScreenUpdating = False
ShtArr = Array("Opening Balances", "Sales Data", "Receipts")
ReDim Temp(1 To 1000, 1 To 6)
With Sheets("Customer Statement")
.Rows(10 & ":" & .Rows.Count).Delete
Cust = .Range("A6").Value
For i = LBound(ShtArr) To UBound(ShtArr)
With Sheets(ShtArr(i))
Data = .Cells(1).CurrentRegion
For ii = 2 To UBound(Data, 1)
If i = 0 Then
dt = Format(DateSerial(Year(Date), Month(Date), 1), "dd/mm/yyyy")
If Data(ii, 2) = Cust Then x = x + 1: Temp(x, 1) = dt: Temp(x, 6) = Data(ii, 4)
ElseIf i = 1 Then
If Data(ii, 3) = Cust Then
x = x + 1
Temp(x, 1) = Data(ii, 1)
Temp(x, 3) = Data(ii, 5)
Temp(x, 4) = Data(ii, 16)
End If
Else
If Data(ii, 3) = Cust Then
x = x + 1
Temp(x, 1) = Data(ii, 1)
Temp(x, 2) = Data(ii, 5)
Temp(x, 3) = Data(ii, 6)
Temp(x, 5) = Data(ii, 7)
End If
End If
Next ii
End With
Next i
.Range("A10").Resize(x, 6) = Temp
With .Range("A10:F" & .Cells(.Rows.Count, 1).End(xlUp).Row)
.Resize(.Rows.Count - 1).Columns(6).Offset(1).Formula = "=sum(F10-E11+D11)"
.Sort .Range("A10"), xlAscending
.Borders.LineStyle = xlContinuous
End With
End With
Application.ScreenUpdating = True
End Sub

[sunboy]

Hi

Please find the attached excel sheet including the above VBA code. I hope its now clear.

Thank you.

Regards.

Sunboy

[oeldere]

Please add the code between tags #, according to the forumrules.

[Vraag en antwoord]

Change

Please Login or Register  to view this content.

to the number off rows and columns jou need.

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[sunboy]

hi

what are code tags?

[sunboy]

Hi

My apologies I do not know much about VBA, I got this VBA on this forum after requesting for help. Sintek, the forum expert gave me the above VBA and it worked well till I changed the columns and rows. If I knew what code tags are, I should have given you, but I do not know them.

My apologies again.

Regards

Sunboy

[alansidman]

Read my post on how to add code tags. If you still do not understand then review the rules you agreed to when you joined.

No further help until OP complies with Code Tag insertion.