Microsoft Excel - Input box info to new worksheet

Asked By Lionell on 05-Mar-13 04:20 PM

Hi,

I have a macro that I would like to input text or number to a new work sheet into any cell and if need have the text wrap to the cell. The code I have so far is shown below but not working can you please help. Thank you in advance.

Sub myBuildList()
Dim lngLstRow&
Dim strMyIPBMsg$

strMyIPBMsg = InputBox("User-Name")

With Sheets("Sheet2")
lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row
.Range("B10" & lngLstRow).Value = strMyIPBMsg
End With

Sheets("Sheet1").Select
End Sub

John D replied to Lionell on 05-Mar-13 06:19 PM

Hi
Try this :
Sub myBuildList()
Dim lngLstRow&
Dim strMyIPBMsg$

strMyIPBMsg = InputBox("User-Name")

With Sheets("Sheet2")
lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row
.Range("B" & lngLstRow).Value = strMyIPBMsg
End With

Sheets("Sheet1").Select
End Sub

Harry Boughen replied to Lionell on 05-Mar-13 06:27 PM

Hello Lionell,
Try
Sub myBuildList()
Dim lngLstRow As Long
Dim strMyIPBMsg As String

strMyIPBMsg = InputBox("User-Name")

With Sheets("Sheet2")
lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row
.Range("B" & lngLstRow).Value = strMyIPBMsg
End With

Sheets("Sheet1").Select
End Sub

NOTE: The abbreviated Dim statements are generally not used unless necessary for some compatability reasons and are actively discouraged these days.

Regards
Harry

Lionell replied to John D on 06-Mar-13 12:23 PM

Thanks John!!

Lionell replied to Harry Boughen on 06-Mar-13 12:24 PM

Thanks Harry!!

John D replied to Lionell on 06-Mar-13 02:26 PM

You're welcome
Thanks for the feedback

Check each line for match - Lionell replied to John D on 11-Mar-13 05:59 PM

Hello John,

I have code that I want to check each line looking for the word "UNMET" in the FE Column starting at row 2 and when found copy and paste section of that over to a pre-modified template on the result worksheet. So far with help of this forum I was able to get it to do some of it but for some reason it's not checking each row and not copying and pasting say for instance row two and three section and making a new template, row nine and ten and making a new template ect, ect. Also if possible their sub sections to each for instance row 4,5,6,7,8 ect, ect to be added to the template section if needed by having a input box pop up and ask "Are there any additional lines needed to be added to that section and if so copy and paste those section to the template. Please see attachment. I know the second part seems very difficult and I can't imagine how to do it, but I know that this forum loves a challenge and can solve anything. Thank you in advance.

Completed SC_Monitoring_Tool-_SC_(11-30-12)_(2) (version 1) (Autosaved).zip