For Loop until empty cell

[alex_shin]

Hi All,

I am trying to get the script to copy the chart and paste as picture using data from a list (offset List until empty cell).

The VBA below ran successfully but did not do the 1st FOR LOOP that I wanted it to.


Sub xxx()

'
' Prevents Screen Refreshing
Application.ScreenUpdating = False

Worksheets("Sheet3").Select
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

Worksheets("Sheet1").Select
Columns("A:A").Select
Selection.Copy
Sheets("Sheet3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range("$B$1:$B$385").RemoveDuplicates Columns:=1, Header:=xlYes
Range("B2:B37").Select
Selection.Copy
Sheets("Sheet2").Select
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("B6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(R2C2="""","""",SUMIFS(Sheet1!C[1],Sheet1!C1,R2C2,Sheet1!C2,RC1))"

Dim x As Integer
' Set Numrows = number of rows of data
numrows = Range("I2", Range("I2").End(xlDown)).Rows.Count
' Select cell B2
Range("B2").Select
' Establish "For" loop to loop "numrows" number of times
For x = 1 To numrows

ActiveCell.FormulaR1C1 = "=RC[7]"

Dim arange As Range

Range("A5:F29").Select
Application.CutCopyMode = False
Selection.Copy
Range("A38").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set arange = Range("A38:F62")

For Each cell In arange
If cell.Text = "#N/A" Then
cell.ClearContents
End If

Next
Next

End Sub

[alex_shin]

Anyone Please help...

[alex_shin]

Anyone...please

[Kaper]

Hi,
First of all - follow the rules. Edit your post and use code tags. If you need additional explanation - visit http://www.excelforum.com/forum-rule...rum-rules.html

Then merit: I think that the main issue with "not working loop" is that you refer to active cell, whle you do not change it.
If the rest of code works fine and you want the loop go through the sells in column B then probably instead of

Please Login or Register  to view this content.

you shall use:

Please Login or Register  to view this content.

[alex_shin]

Thanks Kaper for your contribution.

I am using the formula to read a distinct list on column I.

And your advice work but it only copied one picture. I required a VBA to be able to copy the chart, based on the list on Column I, and paste as pictures.

[zbor]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)