SOLVED

Can anyone see the error in this VBA code?

%3CLINGO-SUB%20id%3D%22lingo-sub-1429503%22%20slang%3D%22en-US%22%3ECan%20anyone%20see%20the%20error%20in%20this%20VBA%20code%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1429503%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20very%20new%20to%20vba%20code.%20I%20am%20learning%20mainly%20by%20adapting%20existing%20macros.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20screenshot%20here%2C%20you%20can%20see%20a%20line%20of%20code%20that%20isn't%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22nicholasajl_0-1590917677504.png%22%20style%3D%22width%3A%20658px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195558iCFD7CD3F463B4D4C%2Fimage-dimensions%2F658x125%3Fv%3D1.0%22%20width%3D%22658%22%20height%3D%22125%22%20title%3D%22nicholasajl_0-1590917677504.png%22%20alt%3D%22nicholasajl_0-1590917677504.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20code%2C%20as%20a%20whole%2C%20creates%20a%20new%20sheet%20for%20each%20row%20of%20data.%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20sparkline%20in%20cell%20A20.%20The%20problem%20is%20how%20to%20sourcedata%20range.%20It%20is%20always%20columns%20BK%20to%20CW%2C%20but%20I%20need%20to%20tell%20it%20how%20to%20use%20just%20one%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%26amp%3BRng.Row%20operation%20works%20on%20all%20other%20lines%2C%20but%20not%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENick%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1429503%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1429539%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20anyone%20see%20the%20error%20in%20this%20VBA%20code%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1429539%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684684%22%20target%3D%22_blank%22%3E%40nicholasajl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20you%20are%20supplying%20a%20row%20reference%20to%20only%20the%20CW%20column%20of%20MasterSheet!BK%3ACW%2C%26nbsp%3Bbut%20the%20BK%20column%20also%20requires%20a%20row%20reference%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E%22MasterSheet!BK%22%20%26amp%3B%20Rng.Row%20%26amp%3B%20%22%3ACW%22%20%26amp%3B%20Rng.Row%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1429561%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20anyone%20see%20the%20error%20in%20this%20VBA%20code%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1429561%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20minutes%2C%20you've%20fixed%20what%20I've%20spent%20many%20hours%20trying%20to%20sort.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20so%20grateful!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENick%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1429569%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20anyone%20see%20the%20error%20in%20this%20VBA%20code%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1429569%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684684%22%20target%3D%22_blank%22%3E%40nicholasajl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20very%20welcome!%20You're%20doing%20the%20right%20thing%20by%20trying%20to%20adapt%20code%20yourself%20in%20order%20to%20develop%20your%20understanding%20of%20VBA.%20Best%20way%20to%20learn%2C%20in%20my%20opinion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello All,

 

I am very new to vba code. I am learning mainly by adapting existing macros.

 

On the screenshot here, you can see a line of code that isn't working.

 

nicholasajl_0-1590917677504.png

This code, as a whole, creates a new sheet for each row of data.

I want to create a sparkline in cell A20. The problem is how to sourcedata range. It is always columns BK to CW, but I need to tell it how to use just one row. 

 

The &Rng.Row operation works on all other lines, but not here.

 

Can anyone help?

 

Thanks,

 

Nick

 

3 Replies
Highlighted
Best Response confirmed by nicholasajl (New Contributor)
Solution

@nicholasajl 

 

Hi,

 

Currently you are supplying a row reference to only the CW column of MasterSheet!BK:CW, but the BK column also requires a row reference:

 

"MasterSheet!BK" & Rng.Row & ":CW" & Rng.Row

 

Regards

Highlighted

@Jos_Woolley 

 

In minutes, you've fixed what I've spent many hours trying to sort.

 

I'm so grateful!

 

Many thanks,

 

Nick

Highlighted

@nicholasajl 

 

You're very welcome! You're doing the right thing by trying to adapt code yourself in order to develop your understanding of VBA. Best way to learn, in my opinion.

 

Regards