Previous Topic Next Topic

Quick question on VB code

Solved/Closed
weenie - Apr 8, 2011 at 10:51 AM
 weenie - May 5, 2011 at 02:46 PM
Hello,

Basically, I have 4 columns (A-D). Columns C & D will always have information in cells since I am bringing that info in from another sheet. Then I am using a Input Box to ask to type in what name this information is called on column B and to use Column C to find last row so Column B can know where to stop at when copying the name I just asked it. It works fine when starting off (no data in column B at all) BUT my problem is the last line of code. It will replace (overwrite) existing information at cell B2 even though (example at B10 or longer it will never be the same number of cells). The code does find last cell when I enter the Name to input but I need to then copy to last row using Column C as "helper" not start all over again at B2:B and overwrite info all ready there.

'Ask the Type of XLap & put into Column B
Range("B2").Select
x = InputBox("Enter the Type of XLap (example, LVN Overlap, for cell B2")
If x <> "" Then Range("B65536").End(xlUp).Offset(1, 0).Value = x
Lastrow = Cells(Rows.Count, 3).End(xlUp).Row
Selection.Copy Destination:=Range("B2:B" & Lastrow)

Thanks
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 11, 2011 at 09:39 AM
Hello Weenie,

Having trouble understanding you.
You want to input a value in column B using an inputbox based on the value in column C.
To do this, you just enter a value in B2, hit enter, enter next value and so on.... Or remove the last two code lines if you want to use the inputbox.

I'm clearly missing something.

Can you re-explain your issue with maybe an example of how you sheet looks like or even post your workbook using a site like www.speedyshare.com.

Best regards,
Trowa
0
weenie
Apr 11, 2011 at 10:26 AM
http://www.speedyshare.com/files/27904792/Example.xlsm

Order Type Name Values
Dog -432
Horse 3223
pig 332
owl -0.23
cat 1.2
rat 0.546
lion 0.47749
zebra 0.4321
sheep 0.6576
cow 3.756
tiger 70
fish 76
ant 45
rhino 32
beetle 21
Basically, after copying the name/values from another sheet, I have input box asking to enter the "type" and using the "Name" column to autofill to lastrow. It works initially BUT my problem is I am copying "Name/Values" in increments and prompting input box each time since "Type & Order" will be different. Example, I brought in data under "Name/Values" up to 'Lion' and input box asks me to enter "Type" and I enter, 'Home'. Then I bring in next batch data of "Name/Values" (will be placed after 'Lion') and now prompt input box for "Type" & enter 'Bus' and want it to autofill/copy to the last row in Name which would be beetle. What is happening when i bring in this second set, is it replaces everything in "Type" I entered previously. Hope this helps and thanks for responding. I'm at a loss how to fix this.
Thanks,
weenie
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 12, 2011 at 09:32 AM
Hi Weenie,

Don't know what you uploaded (some sort of zip file?), but your story made your query clear to me.

Try this code: 

Sub Test()
Lastrow = Cells(Rows.Count, 3).End(xlUp).Row
x = inputbox("Enter the Type of XLap (example, LVN Overlap, for cell B2")
If x = "" Then Exit Sub
Set MR = Range("B2:B" & Lastrow)
For Each cell In MR
If cell.Value = "" Then cell.Value = x
    Next
End Sub

Does this work for you?

Best regards,
Trowa
0
weenie
May 5, 2011 at 02:46 PM
Sorry took so long for feedback. Yes it works like a charm thank you so much for your help, it's greatly appreciated.
Thanks,
weenie
0

Similar discussions

Unlock code
Priya -
HelpiOS -

1 response
Keyboard troubles
jimbobjoe -
car -

38 responses
How to unlock Gameloft games
Kefas -
HelpiOS -

3 responses
lava hard reset & factory reset
AVAY -
Pnr -

2 responses