How to fix a vba code that returns a cell reference

How this works is if data is input in the next available cell in a column this code sees what cell it was and in another of area of the sheet input the cell location like =YXX the XX being the cell number. and Y being the Column

using the nextrw from this line

Please Login or Register  to view this content.

I was able to come up with this

Please Login or Register  to view this content.

Which does work but need to edit it so if the main cell is blank or has a -this would return a blank I tried

Please Login or Register  to view this content.

kind of a hail mary

So went back to what worked before but the code looks ridiculous and wonder can someone please fix this so it looks like a formula that is easier to edit. Dont laugh too hard but here is what I came up with for a simple formula that should come out looking like this sampel =IF(YXX<"",YXX,"") the YXX are what the script finds using the NextRw

Please Login or Register  to view this content.

It does work just hard to read and edit.

Copy of the stripped down workbook

Can we see the code in a sample workbook please?

If I'm honest, I'm not 100% sure what you are trying to do. But maybe this will point you in the right direction:

Please Login or Register  to view this content.

Regards, TMS

Its very hard to explain so I think that is why im confusing people.
The userform will input the data and data only in two place how it works now. Problem with this is if I edit the main location range 7-190 I have to edit the second location to match. I need the second area as it performs the calculations. This second area is after row 202

So for example if the next available area for the userform to input data is row 7 than (txtPointsReceived) is input into D7 so would have a value of say 97
Now around row 200 there are 5 columns all with a header that match the assignment type picked in the userform. The (txtPointsReceived) get put into the corresponding column.
I want to change that part instead of it adding a value say 97 I want it to install in this example =IF(D7<"",D7,"") the D7 is the part I am having an issue with I cant get the script to echo the cell that the first value was put in.
This line is the one I am having an issue with. If try to input the formula here I get errors. I found if I put "=" & Chr(68) & NextRw it works but that is an example D7 so in order to get the rest of the formula to work with no vba error I had to use a ton of Chr for the parenthesis and commas letters etc.

Please Login or Register  to view this content.

' *********************************************************************
' Data input for active gradebook weighted key
' *********************************************************************
Below this are the rows 200+ the Key I call it which the problem area. In your sample you edited the area above it.



In a nut shell I would like this line of code to go from this

Please Login or Register  to view this content.

To something like this

Please Login or Register  to view this content.

No clue what to put for the ?????

If I try something like

Please Login or Register  to view this content.

I get "Run-Time Error 1004: Application-defined or object-defined error" with

without the parenthesis I get a run time error 13 type mismatch

OK, now I understand what you're trying to do. What I don't really understand is why you're trying to do it by inserting formulae.

You're driving it with code so you may as well just work out what you need to store and where you need to store it and put it there.

I've commented out all the lines of code you use to put a formula in place and added this code at the end of the subroutine.

Please Login or Register  to view this content.

See the updated example


Regards, TMS

Sorry ... forum seems to be dumping on the upload. I'll try again now. If that fails, maybe tomorrow.

Regards, TMS

Maybe, this:

Please Login or Register  to view this content.

Becomes this:

Please Login or Register  to view this content.

Regards, TMS

Sorry, too late for me to do it right now ... but later. Don't think it wii be too difficult.

Regards, TMS

OK, I think this works:

Please Login or Register  to view this content.

See the updated sample workbook.

Regards, TMS

TMS that one confused me I tried to use it but all the data goes into A:E. However I think I have officially lost my mind either or one of two things happened the code fairy came and visited me last night or Excel is messing with me. I am leaning towards the latter. After tinkering with your code

Please Login or Register  to view this content.

And trying to adapt it to work for my need doing things like

Please Login or Register  to view this content.

so it would only work with columns G:K adding getting undefined expecting array errors and many others which is normal when I edit anything.
For some reason I opened one of the other forms that had the previous code you wrote in it and it was working. So after almost an hour of testing, closing excel opening it back up and rebooting to see if it would act up again it hasnt.

So can you test this on your system to see if it works for you please.
The paper and pencil image opens the corresponding form for that group of columns. It should input all the data within those columns and insert the points received in the key after row 200. There are 3 images are for 3 different forms each controls one of the column groups. Each column group is a course so there are 3 courses.

My excel did update lastnight so that might be why it is working now and I have 2013 if it makes a difference.

The workbook

Thank you for looking

Please Login or Register  to view this content.

This line of code is used to determine the column letter to be used for the formula relative to a particular column. So, for columns 1 to 5, it will use "D", 7 to 11 will use "J", and 13 to 17 will use "P".

So, you start with determining the numeric column number, lCol using a Match against the headings in Row 200. This (lCol) is used to select the text column letter, sCol from the array of possible column letters. It should be generic. That is, provided your drop down box has the appropriate headings, it should find the correct column and column letter fro the formula.

I'll have a look but it seems, at first glance, as though you have reverted to the original "static" code.

Thanks again fro the rep.

Regards, TMS

Ok, generic code applied to all three forms.

Please see the attached updated workbook.

Regards, TMS

You're welcome.

I didn't change any of the logic, just dropped it back in ... same place each module. And you'd defined some of the variables in some of the modules, but not all of the variables in all of the modules do I got some compilation errors. But, I've set them all up in the same way so you shouldn't have any issues.

A lot of the code is simply repeated under each form so, in theory, you could/should make generic modules and call from the form code. But I'm afraid that's just a step too far

Cheers, TMS

That's your code, not mine. Not sure what you want me to say.

My code does not specifically refer to a column letter. It picks it up from the array. It is generic code that should be applied, and work, for each form.

Regards, TMS

Sorry, just realised it WAS my code from an earlier version. But as you note, if you use the same column headings, it will always return the first match.

You could, perhaps, get around this by doing the match in a subset of row 200 but have an increment for each form. You'd add the increment to the value to get the true column.

Regards, TMS

See the re-worked example. This works even if the Column Headers are the same in each Form.


Regards, TMS

You're welcome. Thanks for the rep.