Code that grabs a dynamic range from a cell

**amartin575** · 03-27-2014, 09:50 AM

Hello,

I have a file of data with sales information. A test is run over the data. Adjacent to the test I have an Address formula that gives me a result of a tab name and a range. It does this on any row that fails the test. This range in column DL is telling me where I need to go in the data and cut that range out of the data. The range in DK is telling me where I need to paste that cut data too. I need code that will loop through and skip over the blanks (data that passed the test) and perform the action of cut and paste on the ranges specified in each row of data that failed the test. I am having a hard time getting the code to use the value in the cell as the range. Any help would be appreciated.

this code below goes through and cuts the data in cell DL and pastes it to DK. That isn't exactly how I want this to work. I want it to use the range in the cell as the location to go cut and use the range in the adjacent cell as the range to go paste to.

Sub amarting575aazz()
Dim rcell As Range
For Each rcell In Range("DL9:DL" & ActiveSheet.UsedRange.Rows.Count + 1)
    If rcell.Value <> "" Then rcell.Value.Cut rcell.Value.Offset(, -1)
Next rcell
End Sub

**JBeaucaire** · 03-27-2014, 10:26 AM

It appears all your code is doing is copying the values down from the row above, correct? If so, try this:

Sub CopySpecial()
Dim MyArr As Variant, rcell As Range

For Each rcell In Range("DL9:DL10000").SpecialCells(xlConstants)
    If Len(rcell) > 0 Then
        MyArr = Split(rcell.Text, "!")
        With Sheets(Replace(MyArr(0), "'", "")).Range(MyArr(1))
            .Copy .Offset(1)
            .ClearContents
        End With
    End If
Next rcell

End Sub

**Kaper** · 03-27-2014, 10:48 AM

from the description it seems to me that

Sub test()
Dim rcell As Range
For Each rcell In Range("DL9:DL" & ActiveSheet.UsedRange.Rows.Count + 1)
    If rcell.Value <> "" Then Range("'" & rcell.Value).Cut Range("'" & rcell.Value.Offset(, -1))
Next rcell
End Sub

shall do.
Of course instead of concatenating opening ' with the content of the cell, you can use split and replace to properly construct sheets() and range() references as above.

**amartin575** · 03-27-2014, 05:11 PM

Thanks JB! It worked the way we wanted it to. I appreciate the help.

Kaper I didn't try yours but I will once I get a chance.

Code that grabs a dynamic range from a cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Code that grabs a dynamic range from a cell

Re: Code that grabs a dynamic range from a cell

Re: Code that grabs a dynamic range from a cell

Re: Code that grabs a dynamic range from a cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA code for Dynamic range

[SOLVED] Need help with Dynamic Range in my code.

How can I fix the below code for establishing a dynamic range?

How do i reference a dynamic cell range in VBA code

VBA code for dynamic name range

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions