Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ I Can Not Get My Loop To Work

Posted by: wornout May 21 2020, 07:49 PM

How do I get my loop to work I am trying to say if a cell Column C is not blank and the cell in column B is then add the following I am also trying to get it to add 1 to each nextnumberTasks
I am coping and pasting data from one workbook to another I am pasting into rows underneath data I am trying to add to the rows I have just pasted. The pasted part works and it puts the right things in the first row but if pasting more that one row it does not put anything in and it keeps running so my workbook crashes

Dim wb As Workbook, NR As Long
Set wb = Workbooks.Open("C:\Users\Michelle\Documents\CRM\Task_Manager.xlsm")
NR = wb.Sheets("Tasks").Range("C" & Rows.Count).End(xlUp).Row + 1
LastRow = ThisWorkbook.Sheets("ToTaskManager").Range("A" & Rows.Count).End(xlUp).Row
With ThisWorkbook.Sheets("ToTaskManager")
Dim cell As Range
nextnumberTasks = Application.WorksheetFunction.Max(wb.Sheets("Tasks").Range("B4:B" & NR)) + 1
.Range("A2:A" & LastRow).Copy Destination:=wb.Sheets("Tasks").Range("C" & NR)
.Range("A2:A" & LastRow).Copy Destination:=wb.Sheets("Tasks").Range("E" & NR)
.Range("B2:B" & LastRow).Copy Destination:=wb.Sheets("Tasks").Range("K" & NR)
End With
Debug.Print nextnumberTasks

nextnumberTasks = Application.WorksheetFunction.Max(wb.Sheets("Tasks").Range("B4:B" & NR))

For Each cell In wb.Sheets("Tasks").UsedRange.Cells
If wb.Sheets("Tasks").Range("B:B").Value = " " And wb.Sheets("Tasks").Range("C:C").Value <> " " Then

wb.Sheets("Tasks").Range("B" & NR) = nextnumberTasks + 1
wb.Sheets("Tasks").Range("A" & NR) = "Task" & wb.Sheets("Tasks").Range("B" & NR).Value
wb.Sheets("Tasks").Range("J" & NR) = "Pending"
End if
Next cell

Posted by: June7 May 21 2020, 08:22 PM

Where are you running this code - Excel or Access VBA?

If you want to provide workbook so we can more effectively test, follow instructions at bottom of my post.