Help with Code possibly integer related

[stroffso]

Hi All,

I have the below piece of code which essentially takes data from one tab and makes it a bit more linear on another. So all the data (which is a timesheet) is on the timesheet tab. The code then changes it over to the history tab in a more linear form. Never had an issue with this until I went to add some columns which I had defined previously as “for k = 73 to 81” and made it to “for k = 73 to 101” which then did not work. However when I changed it to “for k = 73 to 90” it all worked fine and did everything as it should. When I went to “for k = 73 to 91” it did not work. Can anyone tell me what I am doing wrong here?

Sub Releasedarn()







Dim i As Integer 'count of rows in History Sheet



Dim j As Integer



Dim k As Integer ' count column



Dim l As Integer 'Sheet History Counter



Dim m As Integer



Dim x As String



Dim wb As Workbook











For k = 73 To 90



For j = 12 To 135



Set myRange = Sheets(“History”).Range("A:A")



i = Application.WorksheetFunction.CountA(myRange)



i = 1 + i



If (Sheets(“Timesheet”).Range("AN" & j).Value) = "Ready" Then



If (Sheets(“Timesheet”).Range(Chr(k) & j).Value) = "" Then







Else



'Copies all data to the history tab











Sheets("History").Range("A" & i).Value = Sheets(“Timesheet”).Range("C2").Value



Sheets(“History”).Range("B" & i).Value = Sheets(“Timesheet”).Range("F3").Value



Sheets(“History”).Range("C" & i).Value = Sheets(“Timesheet”).Range("F2").Value



Sheets(“History”).Range("D" & i).Value = "=Text(C" & i & ",""mmm"")"



Sheets(“History”).Range("E" & i).Value = Sheets(“Timesheet”).Range("C" & j).Value



Sheets(“History”).Range("F" & i).Value = Sheets(“Timesheet”).Range("B" & j).Value



Sheets(“History”).Range("G" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & "4").Value



Sheets(“History”).Range("H" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & "5").Value







Sheets(“History”).Range("I" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & "6").Value



Sheets(“History”).Range("J" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & "7").Value







Sheets(“History”).Range("K" & i).Value = Sheets(“Timesheet”).Range("A" & j).Value



Sheets(“History”).Range("L" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & "8").Value



Sheets(“History”).Range("M" & i).Value = "\" & Sheets(“Timesheet”).Range(Chr(k) & "7").Value & "." & Sheets(“Timesheet”).Range("A" & j).Value & Sheets(“Timesheet”).Range(Chr(k) & "8").Value



Sheets(“History”).Range("N" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & j).Value



Sheets(“History”).Range("O" & i).Value = Sheets(“Timesheet”).Range("H" & j).Value







If j > 74 Then



Sheets(“History”).Range("P" & i).Value = Sheets(“Timesheet”).Range("F" & j).Value



Sheets(“History”).Range("Q" & i).Value = Sheets(“Timesheet”).Range("e" & j).Value



End If







Sheets(“History”).Range("R" & i).Formula = "=N" & i & "*O" & i



Sheets(“History”).Range("S" & i).Value = Sheets(“Timesheet”).Range("AM" & j).Value



Sheets(“History”).Range("T" & i).Value = "No" 'comment for reciepting







End If



End If







Next j



Next k

End Sub

 

[stroffso]

You problem is that you are converting the value of k into a character, that is then being used as the column letter for a range

Range(Chr(k) & j).

Character 90 is Z, but character 91 is [ which is not a valid column.
Change this For k = 73 To 90 to For k= 9 to 37 and then wherever you have Range(Chr(k) & j) change it to Cells(j,k)

Perfect, that worked a treat thank you so much

 

[stroffso]

What exactly does "doesn't work" mean? What happens?

Was getting a 1004 application defined or object defined error which then highlighted this row as the problem

If (Sheets("TimeSheet").Range(Chr(k) & j).Value) = "" Then

Have it all fixed now though I think thanks to everyones help here thanks again to you all

 

[Fluff]

Glad we could help & thanks for the feedback