X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> I Can Not Get My Loop To Work, Office 2010    
post May 21 2020, 07:49 PM

Posts: 1,360
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

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    6th July 2020 - 07:24 PM