VBA Addng Record to Named Range

Status
Not open for further replies.
P

Patchworks

New Member
Joined
Jul 15, 2009
Messages
23
I have the following code and would like to have it inserted in to a named range when saved? It only adds the data to the last empty row and not in to the range!

Since it does not add to my named range, it causes problems in other parts of the worksheet!

Code: 
Private Sub Save_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Forums Postings")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = frmForumsPosting.ForumID.Caption
ws.Cells(iRow, 2).Value = frmForumsPosting.ForumURL.Caption

End Sub

Thanks in advance for your help,

g
 
Sort by date Sort by votes
You can easily update your named range.. Here's a sample of how I do it after adding a record

Code: 
        ActiveWorkbook.Names.Add Name:="Bottle_List", RefersToR1C1:= _
                                 "='Invoice Data'!R1C1:R" & LTrim(Str(Last_Row("'Bottle Data'!A"))) & "C1"
 
Upvote 0
Thanks everyone for the help...

phxsportz said:
You can easily update your named range.. Here's a sample of how I do it after adding a record

Code: 
        ActiveWorkbook.Names.Add Name:="Bottle_List", RefersToR1C1:= _
                                 "='Invoice Data'!R1C1:R" & LTrim(Str(Last_Row("'Bottle Data'!A"))) & "C1"
Click to expand...

Phxsportz, wow, that is way to complicated for me to modify for my code. Would it help if I posted my code? Could you modify it for my references?

pbornemeier said:
This thread discusses dynamic named ranges, which may help you to fix your problem (see post #8):
http://www.mrexcel.com/forum/showthread.php?t=33856
Click to expand...

pbornemeiier, again this way above my head.. Thanks for the information tho. It helped me understand a little bit..

pjoaquin said:
Cross-posted here: http://www.excelforum.com/excel-programming/691636-insert-userform-data-in-named-range.html
Click to expand...

pjoaquin, What? Are you the internet Police? To my knowledge there is nothing wrong with being a member of several related boards! And common sense says it you don't get an answer one board, try another. So respectfully, if you don't have anything to offer the thread, please don't hijack it. Thank you!

To everyone who tried to help, thanks agian for your help.

g
 
Upvote 0
Patchworks said:
pjoaquin, What? Are you the internet Police? To my knowledge there is nothing wrong with being a member of several related boards! And common sense says it you don't get an answer one board, try another. So respectfully, if you don't have anything to offer the thread, please don't hijack it. Thank you!
Click to expand...
You are absolutely wrong.
This board doesn't care much about crossposting, but most of the other site are clearly mentioning about it in their rules.
You should have read their rules when you join.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
Range("a1", Range("a" & Rows.Count).End(xlUp)).Name = "Bottle_List"
End Sub
 
Upvote 0
I'm not the internet police, I'm a moderator at ExcelForum.com where you cross-posted. You apparently haven't read the forum rules either here or at ExcelForum.com.
MrExcel Cross-Posting Info:
Cross-posting is the term we use when a person has posted the same question to multiple online forums. While there is nothing actually "wrong" with this, it is asked that you at least mention that the question has been posted elsewhere, including a link to the thread on the other forum. There are many people who post at several different forums; threads are cross-posted without stating it are easily identified and pointed out as such.

Read: http://www.excelguru.ca/node/7
Click to expand...
ExcelForum Cross-Posting Info:
Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!
Click to expand...

Your posts there will be locked. You're welcome for the link juice!
 
Upvote 0
Status
Not open for further replies.

Similar threads

O
  • Solved
Finding the last row in sheets automatically
Replies
6
Views
214
Olov
O
O
  • Solved
Hide rows with different values in the same column?
Replies
4
Views
148
Olov
O
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
552
brianv
B
S
  • Solved
Visual Basic code stopped working
3 4 5
Replies
40
Views
1K
MARK858
MARK858
C
  • Question Question
VBA code for new workbook extraction with data grouping
Replies
0
Views
111
coding123456
C

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top