This is very strange what is wrong with this VBA code?

M

marc005

Board Regular
Joined
Apr 21, 2013
Messages
58
Ok I have a worksheet with two sheets: Download and Historical

The Download sheet looks like this:

SSE [% Chg] Aarhuskarlshamn ABB ... kuk
Friday, April 20, 2013 -0.48 -0.29 ... 5


The Historical sheet looks like this:

SSE [% Chg] Aarhuskarlshamn ABB ...
Friday, April 19, 2013 -0.48 -0.29 ...


Later of course the % Chg will be different but right now they are the same (it does not matter).

Note that the Download sheet has one more stock than the Historical sheet.


Now I have made two procedures called Store() and Check()
(See attached files or see below)

You first run Store() and then Check() this will result in that the Historical sheet looks like this:

SSE [% Chg] Aarhuskarlshamn ABB ...
Friday, April 19, 2013 -0.48 -0.29 ...
Friday, April 20, 2013 -0.48 -0.29 ...

SSE [% Chg] Aarhuskarlshamn ABB ...
Friday, April 20, 2013 -0.48 -0.29 ...

252 1 2

Everything is working except for what happens in the end of Historical sheet. It looks like this:

Yara International ?
3.09
3.09 5

Yara International kuk
3.09 5

297 144

This is very strange because where there is a ? mark the name kuk should appear. This is puzzling.

The two procedures Store() and Check() looks like this:

Code: 
Public Sub Store()
Dim LastrowDownload As Integer
Dim LastcolumnDownload As Integer
Dim LastrowHistorical As Integer

LastrowDownload = Worksheets("Download").Range("A" & Rows.Count).End(xlUp).Row
LastcolumnDownload = Worksheets("Download").Cells(1, Columns.Count).End(xlToLeft).Column
ColLetterDownload = Application.WorksheetFunction.Substitute(Worksheets("Download").Cells(1, LastcolumnDownload).Address(0, 0), "1", "")
LastrowHistorical = Worksheets("Historical").Range("A" & Rows.Count).End(xlUp).Row
'Sheets("Sheet1").Cells(11, 1).Value = ColLetterDownload

If LastrowHistorical = 1 Then
Sheets("Historical").Range("A" & LastrowHistorical & ":" & ColLetterDownload & 2).Value = Sheets("Download").Range("A1:" & ColLetterDownload & 2).Value
LastrowHistorical = Worksheets("Historical").Range("A" & Rows.Count).End(xlUp).Row
End If

If LastrowHistorical <> 1 And Sheets("Download").Cells(2, 1).Value <> Sheets("Historical").Cells(LastrowHistorical, 1).Value Then
Sheets("Historical").Range("A" & LastrowHistorical + 3 & ":" & ColLetterDownload & LastrowHistorical + 3 + 1).Value = Sheets("Download").Range("A1:" & ColLetterDownload & 2).Value
End If:

End Sub



Public Sub Check()
Dim i As Integer
Dim j As Integer

LastcolumnDownload = Worksheets("Download").Cells(1, Columns.Count).End(xlToLeft).Column
LastcolumnHistorical = Worksheets("Historical").Cells(1, Columns.Count).End(xlToLeft).Column
ColLetterHistorical = Application.WorksheetFunction.Substitute(Worksheets("Historical").Cells(1, LastcolumnHistorical).Address(0, 0), "1", "")
ColLetterHistoricalNew = Application.WorksheetFunction.Substitute(Worksheets("Historical").Cells(1, LastcolumnHistorical + 1).Address(0, 0), "1", "")
LastrowHistorical = Worksheets("Historical").Range("A" & Rows.Count).End(xlUp).Row

'Worksheets("Historical").Cells(20, 1).Value = ColLetterHistoricalNew & 2 & ":" & ColLetterHistoricalNew & LastrowHistorical - 4
'Worksheets("Historical").Cells(21, 1).Value = LastcolumnHistorical

For j = 1 To LastcolumnDownload Step 1
If IsError(Application.Match(Worksheets("Historical").Cells(LastrowHistorical - 1, j).Value, Sheets("Historical").Range("B1:" & ColLetterHistorical & 1).Value)) = True Then
Worksheets("Historical").Cells(10, j) = 0
Worksheets("Historical").Cells(1, LastcolumnHistorical + 1).Value = Sheets("Historical").Cells(LastrowHistorical - 1, j).Value
Worksheets("Historical").Cells(LastrowHistorical - 3, LastcolumnHistorical + 1).Value = Sheets("Historical").Cells(LastrowHistorical, j).Value

'For i = 2 To LastrowHistorical - 4 Step 1
'Worksheets("Historical").Cells(i, LastcolumnHistorical + 1).Value = 0
'Next i

LastcolumnHistorical = Worksheets("Historical").Cells(1, Columns.Count).End(xlToLeft).Column
ColLetterHistorical = Application.WorksheetFunction.Substitute(Worksheets("Historical").Cells(1, LastcolumnHistorical).Address(0, 0), "1", "")
ColLetterHistoricalNew = Application.WorksheetFunction.Substitute(Worksheets("Historical").Cells(1, LastcolumnHistorical + 1).Address(0, 0), "1", "")

Else
Worksheets("Historical").Cells(LastrowHistorical - 3, j).Value = Sheets("Historical").Cells(LastrowHistorical, j).Value
Worksheets("Historical").Cells(10, j) = Application.Match(Worksheets("Historical").Cells(LastrowHistorical - 1, j).Value, Sheets("Historical").Range("B1:" & ColLetterHistorical & 1).Value)
End If

Next j
End Sub

I have tried to find an error but I cant find one. Can anyone spot the problem?

https://skydrive.live.com/redir?resid=1119584707219896!644&authkey=!ACXcJOqW17OU5d4
https://skydrive.live.com/redir?resid=1119584707219896!643&authkey=!APe6K_ETT4MwhNo
https://skydrive.live.com/redir?resid=1119584707219896!645&authkey=!AL3e1antXDlOEtI
 
Last edited by a moderator:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
The Download sheet looks like this:


<colgroup><col style="mso-width-source:userset;mso-width-alt:10422;width:214pt" width="285"> <col style="mso-width-source:userset;mso-width-alt:9142; width:188pt" span="4" width="250"> </colgroup><tbody>
[TD="class: xl65, width: 285"]SSE [% Chg][/TD]
[TD="class: xl65, width: 250"]Aarhuskarlshamn[/TD]
[TD="class: xl65, width: 250"]ABB
[/TD]
[TD="class: xl65, width: 250"]...
[/TD]
[TD="class: xl65, width: 250"]kuk
[/TD]

[TD="class: xl65"]Friday, April 20, 2013[/TD]
[TD="class: xl65"]-0.48[/TD]
[TD="class: xl65"]-0.29[/TD]
[TD="class: xl65"]...
[/TD]
[TD="class: xl65"]5
[/TD]

</tbody>


The Historical sheet looks like this:


<colgroup><col style="mso-width-source:userset;mso-width-alt:9142; width:188pt" span="4" width="250"> </colgroup><tbody>
[TD="class: xl65, width: 250"]SSE [% Chg][/TD]
[TD="class: xl65, width: 250"]Aarhuskarlshamn[/TD]
[TD="class: xl65, width: 250"]ABB[/TD]
[TD="class: xl65, width: 250"]...
[/TD]

[TD="class: xl65"]Friday, April 19, 2013[/TD]
[TD="class: xl65"]-0.48[/TD]
[TD="class: xl65"]-0.29[/TD]
[TD="class: xl65"]...
[/TD]

</tbody>


You first run Store() and then Check() this will result in that the Historical sheet looks like this:


<colgroup><col style="mso-width-source:userset;mso-width-alt:9142; width:188pt" span="4" width="250"> </colgroup><tbody>
[TD="class: xl65, width: 250"]SSE [% Chg][/TD]
[TD="class: xl65, width: 250"]Aarhuskarlshamn[/TD]
[TD="class: xl65, width: 250"]ABB[/TD]
[TD="class: xl65, width: 250"]....
[/TD]

[TD="class: xl65"]Friday, April 19, 2013[/TD]
[TD="class: xl65"]-0.48[/TD]
[TD="class: xl65"]-0.29[/TD]
[TD="class: xl65"]....
[/TD]

[TD="class: xl65"]Friday, April 20, 2013[/TD]
[TD="class: xl65"]-0.48[/TD]
[TD="class: xl65"]-0.29[/TD]
[TD="class: xl65"]....
[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]SSE [% Chg][/TD]
[TD="class: xl65"]Aarhuskarlshamn[/TD]
[TD="class: xl65"]ABB
[/TD]
[TD="class: xl65"].....
[/TD]

[TD="class: xl65"]Friday, April 20, 2013[/TD]
[TD="class: xl65"]-0.48[/TD]
[TD="class: xl65"]-0.29[/TD]
[TD="class: xl65"].....
[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]252[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"].....
[/TD]

</tbody>

and the end of the Historical sheet looks like this:



<colgroup><col style="mso-width-source:userset;mso-width-alt:9142; width:188pt" span="3" width="250"> </colgroup><tbody>
[TD="class: xl65, width: 250"]XANO Industri Series B[/TD]
[TD="class: xl65, width: 250"]Yara International[/TD]
[TD="class: xl65, width: 250"]?
[/TD]

[TD="class: xl65"]-0.83[/TD]
[TD="class: xl65"]3.09[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]-0.83[/TD]
[TD="class: xl65"]3.09[/TD]
[TD="class: xl65"]5[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]XANO Industri Series B[/TD]
[TD="class: xl65"]Yara International[/TD]
[TD="class: xl65"]kuk[/TD]

[TD="class: xl65"]-0.83[/TD]
[TD="class: xl65"]3.09[/TD]
[TD="class: xl65"]5[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"]296[/TD]
[TD="class: xl65"]297[/TD]
[TD="class: xl65"]144[/TD]

</tbody>
 
Upvote 0
Hi Marcus

I've tidied up the code a bit and arrive at the following :-
Code: 
Public Sub Store()
Dim LCDown As Integer
Dim LRDown As Long
Dim LRHist As Long
Dim wsDown As Worksheet
Dim wsHist As Worksheet
Dim ColLtrDown
Dim ColLtrHist
Set wsDown = Worksheets("Download")
Set wsHist = Worksheets("Historical")
LRDown = wsDown.Range("A" & Rows.Count).End(xlUp).Row
LCDown = wsDown.Cells(1, Columns.Count).End(xlToLeft).Column
ColLtrDown = Application.WorksheetFunction.Substitute(wsDown.Cells(1, LCDown).Address(0, 0), "1", "")
LRHist = wsHist.Range("A" & Rows.Count).End(xlUp).Row
'Sheets("Sheet1").Cells(11, 1).Value = ColLtrDown
If LRHist = 1 Then
    Sheets("Historical").Range("A" & LRHist & ":" & ColLtrDown & 2).Value = Sheets("Download").Range("A1:" & ColLtrDown & 2).Value
    LRHist = wsHist.Range("A" & Rows.Count).End(xlUp).Row
End If
If LRHist <> 1 And Sheets("Download").Cells(2, 1).Value <> Sheets("Historical").Cells(LRHist, 1).Value Then
    Sheets("Historical").Range("A" & LRHist + 3 & ":" & ColLtrDown & LRHist + 3 + 1).Value = Sheets("Download").Range("A1:" & ColLtrDown & 2).Value
End If:
End Sub
Public Sub Check()
Dim i As Integer
Dim j As Integer
Dim LCDown As Integer
Dim LCHist As Integer
Dim LRHist As Long
Dim ColLtrDown, ColLtrHist, ColLtrHistNew
Dim wsDown As Worksheet
Dim wsHist As Worksheet
Set wsDown = Worksheets("Download")
Set wsHist = Worksheets("Historical")
LCDown = wsDown.Cells(1, Columns.Count).End(xlToLeft).Column
LCHist = wsHist.Cells(1, Columns.Count).End(xlToLeft).Column
ColLtrHist = Application.WorksheetFunction.Substitute(wsHist.Cells(1, LCHist).Address(0, 0), "1", "")
ColLtrHistNew = Application.WorksheetFunction.Substitute(wsHist.Cells(1, LCHist + 1).Address(0, 0), "1", "")
LRHist = wsHist.Range("A" & Rows.Count).End(xlUp).Row
'wsHist.Cells(20, 1).Value = ColLtrHistNew & 2 & ":" & ColLtrHistNew & LRHist - 4
'wsHist.Cells(21, 1).Value = LCHist
For j = 1 To LCDown Step 1
    If IsError(Application.match(wsHist.Cells(LRHist - 1, j).Value, Sheets("Historical").Range("B1:" & ColLtrHist & 1).Value)) = True Then
        wsHist.Cells(10, j) = 0
        wsHist.Cells(1, LCHist + 1).Value = Sheets("Historical").Cells(LRHist - 1, j).Value
        wsHist.Cells(LRHist - 3, LCHist + 1).Value = Sheets("Historical").Cells(LRHist, j).Value
'For i = 2 To LRHist - 4 Step 1
'wsHist.Cells(i, LCHist + 1).Value = 0
'Next i
        LCHist = wsHist.Cells(1, Columns.Count).End(xlToLeft).Column
        ColLtrHist = Application.WorksheetFunction.Substitute(wsHist.Cells(1, LCHist).Address(0, 0), "1", "")
        ColLtrHistNew = Application.WorksheetFunction.Substitute(wsHist.Cells(1, LCHist + 1).Address(0, 0), "1", "")
    Else
        wsHist.Cells(LRHist - 3, j).Value = Sheets("Historical").Cells(LRHist, j).Value
        wsHist.Cells(10, j) = Application.match(wsHist.Cells(LRHist - 1, j).Value, Sheets("Historical").Range("B1:" & ColLtrHist & 1).Value)
    End If
Next j
'
End Sub

The main thing that strikes me is that you refer to the sheets in different ways eg :-
wsHist.Cells(LRHist - 3, LCHist + 1).Value = Sheets("Historical").Cells(LRHist, j).Value
perhaps it would be best to unify this, and the other similar instances, even though it may not have an adverse effect.

hth
 
Upvote 0
@ukmikeb yes I agree that the code need to be clean up a bit and be more consistent with notations ie
sometime I use Worksheets("Historical") and some time Sheets("Historical"). I will start the clean up process
when everything is working because otherwise I might introduce even more error which makes things even more complex.
 
Upvote 0
Hi Marc

Do you remember?
http://www.mrexcel.com/forum/excel-...-visual-basic-applications-2.html#post3451610

See posts #7 and #11

You need to use 3rd argument = 0 in MATCH Function

So in Check code...

Code: 
Public Sub Check()
Dim i As Integer, LastcolumnDownload As Long, LastcolumnHistorical As Long, LastrowHistorical As Long
Dim j As Integer, ColLetterHistorical As String, ColLetterHistoricalNew As String

LastcolumnDownload = Worksheets("Download").Cells(1, Columns.Count).End(xlToLeft).Column
LastcolumnHistorical = Worksheets("Historical").Cells(1, Columns.Count).End(xlToLeft).Column
ColLetterHistorical = Application.WorksheetFunction.Substitute(Worksheets("Historical").Cells(1, LastcolumnHistorical).Address(0, 0), "1", "")
ColLetterHistoricalNew = Application.WorksheetFunction.Substitute(Worksheets("Historical").Cells(1, LastcolumnHistorical + 1).Address(0, 0), "1", "")
LastrowHistorical = Worksheets("Historical").Range("A" & Rows.Count).End(xlUp).Row
'Worksheets("Historical").Cells(20, 1).Value = ColLetterHistoricalNew & 2 & ":" & ColLetterHistoricalNew & LastrowHistorical - 4
'Worksheets("Historical").Cells(21, 1).Value = LastcolumnHistorical

'[COLOR=#FF0000]See below[/COLOR]
For j = 1 To LastcolumnDownload Step 1
If IsError(Application.Match(Worksheets("Historical").Cells(LastrowHistorical - 1, j).Value, Sheets("Historical").Range("B1:" & ColLetterHistorical & 1).Value, [B][COLOR=#FF0000]0[/COLOR][/B])) = True Then
Worksheets("Historical").Cells(10, j) = 0
Worksheets("Historical").Cells(1, LastcolumnHistorical + 1).Value = Sheets("Historical").Cells(LastrowHistorical - 1, j).Value
Worksheets("Historical").Cells(LastrowHistorical - 3, LastcolumnHistorical + 1).Value = Sheets("Historical").Cells(LastrowHistorical, j).Value
'For i = 2 To LastrowHistorical - 4 Step 1
'Worksheets("Historical").Cells(i, LastcolumnHistorical + 1).Value = 0
'Next i
LastcolumnHistorical = Worksheets("Historical").Cells(1, Columns.Count).End(xlToLeft).Column
ColLetterHistorical = Application.WorksheetFunction.Substitute(Worksheets("Historical").Cells(1, LastcolumnHistorical).Address(0, 0), "1", "")
ColLetterHistoricalNew = Application.WorksheetFunction.Substitute(Worksheets("Historical").Cells(1, LastcolumnHistorical + 1).Address(0, 0), "1", "")
Else
'[COLOR=#FF0000]Also here[/COLOR]
Worksheets("Historical").Cells(LastrowHistorical - 3, j).Value = Sheets("Historical").Cells(LastrowHistorical, j).Value
Worksheets("Historical").Cells(10, j) = Application.Match(Worksheets("Historical").Cells(LastrowHistorical - 1, j).Value, Sheets("Historical").Range("B1:" & ColLetterHistorical & 1).Value, [B][COLOR=#FF0000]0[/COLOR][/B])
End If
Next j
End Sub

Run the code and see what happens

M.
 
Upvote 0
@Marcelo is it to strong (or do you get offended) to say that I love you? (more in a spiritual way though ha ha)
I had so many different version of the codes and I did indeed change the MATCH statement to also include 0 as you
pointed out earlier but I must have missed it in this procedure.

Again, thanx for your input it would have taken a loooooong time for me to spot that :--)
 
Upvote 0
marc005 said:
@Marcelo is it to strong (or do you get offended) to say that I love you? (more in a spiritual way though ha ha)
Click to expand...

I'm not offended, on the contrary. I love you too (more in a spriritual way) :laugh:


(But next time try to not ignore my posts. ;))

I'm not always right but i try my best to help people in this forum.


Again, thanx for your input it would have taken a loooooong time for me to spot that :--)
Click to expand...

You are very welcome.

All the best

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
81
Mazurofgod
M
R
  • Question Question
Having Trouble getting this code in VBA to work. Attempting to download a list of PDFs
Replies
1
Views
125
6StringJazzer
6StringJazzer
M
  • Question Question
What am I doing Wrong?
Replies
6
Views
261
MBarrett
M
J
  • Question Question
Code Issue
Replies
1
Views
188
RoryA
RoryA
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
306
tixarah
T

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top