Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

paccar three problems

  Asked By: Kelley    Date: Nov 29    Category: MS Office    Views: 765
  

Hows it going? I am currently working on a spreadsheet that I
inserted a command button that after I enter the data into the
worksheet (about 1000 lines at a time)manipulates it a little bit,
does some VLookup's from a second worksheet and then updateds a pivot
table on a third worksheet all in the same workbook. In the process
of this I have hit three problems. Help with any of them would be
great.

1. Range function
Currently Range("C" & KeyStartRow).select
I need something like Range(CommentsColumn & KeyStartRow).select
because I worry that other people will rearrange the columns to
something ealier for themselves but I have been unable to figure out
the syntax.

2. Countif
I do three different functions one where I replace some 1's with #;
one were I try to delete duplicate rows, and one after I do the
Vlookup where I try and find all rows with #N/A. I need to know the
syntax where I can look in a specified range and count the instances
so I can use that value in if statments so I know if I need to go
into certain parts of my code or not. And this range is always
changing so it must be dynamically conected to the variables.

3. Message Box
I have a message box pause my code because there is a point where the
user has to add data on the spreadsheet. The problem is that using
the msgbox function I am unable to work on the spreadsheet. I know I
could just make a userform and split the code, but is there a way to
just use the msgbox function?

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Marta Kim     Answered On: Nov 29

> 1. Range function
> Currently Range("C" & KeyStartRow).select
> I need something like Range(CommentsColumn & KeyStartRow).select
> because I worry that other people will rearrange the columns to
> something ealier for themselves but I have been unable to figure out
> the syntax.
>

Try:
cells(KeyStartRow,application.WorksheetFunction.Match("yourColumnHeader",Rows(1)\
,0)).select
This looks for the text "yourColumnHeader" in Row 1 of the active sheet.

> 2. Countif
> I do three different functions one where I replace some 1's with #;
> one were I try to delete duplicate rows, and one after I do the
> Vlookup where I try and find all rows with #N/A. I need to know the
> syntax where I can look in a specified range and count the instances
> so I can use that value in if statments so I know if I need to go
> into certain parts of my code or not. And this range is always
> changing so it must be dynamically conected to the variables.

For a fixed range you could use:
application.WorksheetFunction.CountIf(range("J2:J14"),"#N/A")
or
application.WorksheetFunction.CountIf(range("j2:J14"),"#DIV/0!")

Explore this in the following macro:

Sub blah()
Dim myRange As Range
myCount = Application.WorksheetFunction.CountIf(Range("J2:J14"), "#N/A")
myCount = Application.WorksheetFunction.CountIf(Range("j2:J14"),
"#DIV/0!")
myCount = Application.WorksheetFunction.CountIf(Range("j2:J14"), 1)
Set myRange = Range("J2:J14")
'or replace line above with commented line below for dynamic range
'Set myRange = Range(Cells(2, 10), Cells(14, 10))
'replace the numbers above with variables for column and row numbers
'using the form Cells(rowNo, columnNo)
myCount = Application.WorksheetFunction.CountIf(myRange, "#N/A")
myCount = Application.WorksheetFunction.CountIf(myRange, "#DIV/0!")
myCount = Application.WorksheetFunction.CountIf(myRange, 1)
End Sub

(some lines above might have been split by Yahoo)

>
> 3. Message Box
> I have a message box pause my code because there is a point where the
> user has to add data  on the spreadsheet. The problem is that using
> the msgbox function I am unable to work on the spreadsheet. I know I
> could just make a userform and split the code, but is there a way to
> just use the msgbox function?

I'm not clear on this at all. If you want the msgBox to appear while
code is running to tell the user to enter  something small use the
InputBox function.
Perhaps a modeless user form?

 
Didn't find what you were looking for? Find more on paccar three problems Or get search suggestion and latest updates.




Tagged: