UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> I Can Not Get My Loop To Work, Office 2010    
 
   
wornout
post May 21 2020, 07:49 PM
Post#1



Posts: 1,357
Joined: 17-November 13
From: Orewa New Zealand


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


Go to the top of the page
 
June7
post May 21 2020, 08:22 PM
Post#2



Posts: 1,512
Joined: 25-January 16
From: The Great Land


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.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    2nd July 2020 - 06:03 PM